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

# Calculate a column from two other columns in a table view

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

Hi Marin,

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

If so, you could do the following:

• 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

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

Hi Marin,

Thanks,

Marianna

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

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

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

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

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

Thank you Ankur. Everything works fine now