7 Replies Latest reply: Aug 24, 2016 5:42 AM by mdecau RSS

    Calculate a column from two other columns in a table view

    mdecau

      Hello,

       

      I have two fields : USDAmount (a decimal amount) and Attempted ticket (only with 0 and 1 inside, as a "numeric" flag).

       

      I have a table with these columns as fields (called Visual Members in Datawatch), aggregated as a count for USDAmount and as a sum for AttemptedTickets. This gives me the total number of tickets (because every ticket has a USDAmount) and the total number of attemptedTickets (the sum of each "1").

       

      I would like to get another column, that would be calculated from these two columns: NotAttempted = Count(USDAmount) - Sum(AttemptedTickets).

       

      This seems really simple (in Excel for example, the way do to it is obvious), but I cannot manage to do it. Any help orwould be welcome.

       

      Thank you

        • Re: Calculate a column from two other columns in a table view
          Marianna Romero

          Hi Marin,

          Just to confirm, are you using Datawatch Panopticon Designer for this?

          If so, you could do the following:

          • Edit your Data Table
          • Create a Calculated column (right click on a column and choose New calculated column) and set the expression to:

                              ![Attempted Ticket]

          • Back at the Table, when you drag in the Calculated column into the Visual Members, we should have the NotAttempted value

          Hope I understood your requirements correctly.

           

          Regards,

          Marianna

            • Re: Calculate a column from two other columns in a table view
              mdecau

              Hello Marianna,

               

              Indeed it would work. But I also need to calculate other things, for which this solution would not work. For example, I want a percentage of not attempted for each row. The calculation is: Sum(NotAttempted) / Count (USDAmount) * 100.

               

              I don't see any way of doing that with a calculated column.

                • Re: Calculate a column from two other columns in a table view
                  Marianna Romero

                  Hi Marin,

                  I've informed our Panopticon experts about your question. You should get a reply soon.

                   

                  Thanks,

                  Marianna

                  • Re: Calculate a column from two other columns in a table view
                    Ankur Saraswat

                    Hi Marin,


                    I am happy to inform that Designer allows user to build different computational expressions using the list of available Functions and Measures.

                    In Designer, we can compute the calculation formula Sum(NotAttempted) / Count (USDAmount) * 100 you mentioned, to determine the desired measure column.


                    I've created a sample workbook which demonstrates the utilization of the 'Calculated Fields' and 'Aggregation Method' feature to compute your mentioned measure/formula in Designer.


                    The workbook has two input fields : USDAmount and Attempted ticket (only with 0 and 1 inside, as a "numeric" flag).

                    There are three user created Calculated columns:-


                    Count(USD)  (i.e. Count for USDAmount)

                    Calculation Expression                      : IF(([USD Amount]<>0), 1, 0)

                    Aggregate method selected in Viz     : Count


                    Not-Attempted tickets

                    Calculation Expression                     : IF([Attempted ticket]=0, 1,0)

                    Aggregate method selected in Viz    : CountNonZero


                    Not-Attempted% of CountUSD  (This column is processing your mentioned calculated formula of Sum(NotAttempted) / Count (USDAmount) * 100)

                    Calculation Expression                      : [Not Attempted] / [[CountUSD]] * 100

                    Aggregate method selected in Viz     : PercentOfWeightTotal


                    Please download the sample workbook zip file from the below link. Extract and copy the TestData.xlsx file in your /Datawatch Desktop/Data folder and open the TicketsCalculation.exw workbook in Designer. Kindly review the calculated fields/expressions present in the data table and the calculated results presented in the Table Visualization.

                    https://community.datawatch.com/docs/DOC-2001

                     

                    I trust the above solution resolves your queries. Let me know if there's anything else I can do for you—I'm happy to help.

                     

                    Thanks,

                    Ankur

                      • Re: Calculate a column from two other columns in a table view
                        mdecau

                        Thank you for your answer, Ankur. It's close to what I need, but I think it still does not do what I need. It works fine for the total, but not for each rows of a breakdown.

                         

                        In your example workbook, I added a column "Category" with random values "A" and "B". Then I used this category as a breakdown of your table "Visualizing the Calculated columns" instead of the Auto Key. I have the following results:

                         

                        Category     NotAttempted Ticket     CountUSD     Not Attempt% of Count USD

                        A                    2                                   5                    12.5%   

                        B                    5                                   11                    31.25%

                        Grand Total     7                                   16                    43.75%

                         

                        Here only the last row (Grand Total) is what I need. I would need to get in the last column 40% (2/5) and 45% (5/11).

                          • Re: Calculate a column from two other columns in a table view
                            Ankur Saraswat

                            Hi Marin,

                             

                            My apologies for late reply. I would like to mention that we can very well display the calculation results in the manner you asked for, that is, for each row, including the total row, in a Table viz.

                             

                            As you wished, I added a column "Category" with random values "A" and "B". Then I used this category as a breakdown of the table "Visualizing the Calculated columns" instead of the Auto Key. I have the following results:

                             

                            To achieve the above results, the only change required was switching the "Aggregate method" from PercentOfWeightTotal to WeightedMean for the Not-Attempted% of CountUSD column, in the Table viz.

                             

                            You can download the updated sample workbook from the below link. Extract and copy the TestData.xlsx file in your /Datawatch Desktop/Data folder and open the TicketsCalculation.exw workbook in Designer. Kindly review the calculated fields/expressions present in the data table and the calculated results presented in the Table Visualization.

                            https://community.datawatch.com/docs/DOC-2001

                             

                             

                            I hope the above solves your issue. Please feel free to contact if you have any additional questions.

                             

                             

                            Thanks,

                            Ankur