14 Replies Latest reply: Feb 16, 2015 8:54 PM by DataExploiter _

# Basic calculated formula question in Designer

I need a calculated field to be used in a tree map to measure size. For example I use this in other applications. Sum(X)/Min(Y). Field X needs to be summarized for every record in the chosen beakdown. Divided by the first instance or min /max of field Y. ..Sum(X)/Min(Y). When I create a new calculated field I select my first measure X. This needs to be summarized. The function sum is not available, when I select Y and look for Min or Max the choice is not direct, min(Y) . Instead min(2,3) results is 3. How can I create a field in Designer to be used in a tree map, just like Sum(X)/Min(Y)? Thanks John

• ###### Re: Basic calculated formula question in Designer

Hi,

If you may, please send your inquiry to support@datawatch.com and the Datawatch Global Support Team can open a ticket for you. We will try to replicate the issue and suggest possible solutions.

Thanks,

Kait Riel

Technical Support Supervisor

Datawatch Corporation

• ###### Re: Basic calculated formula question in Designer

Hi,

You can try using the Pivot transform option via the settings of your data source to emulate Sum(X)/Min(Y).

1. Assuming that you already have a workbook, click Edit Data Table.
2. Click on the Settings of your data source.
3. On the Transforms tab, select Pivot transform.
5. Select the desired measure column and your "X" for the Value column.
6. Select Sum for your aggregate.
8. Select the desired measure column and your "Y" for the Value column.
9. Select Min or Max for your aggregate, then click OK.

The two pivots will now be available as options when you create a calculated field.

I hope this solves your problem.

Celle Fetalver

• ###### Re: Basic calculated formula question in Designer

Hi Celle

It does allow me to create those two new fields, one a max value the other

sum. But I do lose the field (Measure Column) that was used to create the

new aggregated measure. It should work but instead I get N/A values for

both new fields, also the new calculated field based on the new pivot

created aggregated measures. Dragging in the new field ties up the computer

for several minutes. Your approach should work but I still have N/A value

and unable to drag in the new calculated field.I will try again latter.

Thanks John

On Wed, Feb 11, 2015 at 12:54 AM, Celle Fetalver <

• ###### Re: Basic calculated formula question in Designer

Hi John,

The workaround I gave you is for time series transformation, where the N/A values in the preview pane are expected. You can use a time series visualization for the time series transformed data set. If the (time series) visualization does not present or provide the information you need, you might need to prepare your data in Modeler instead.

Celle

• ###### Re: Basic calculated formula question in Designer

Any thoughts on why I'm getting N/A for the new pivot fields . See attached

excel file for a clear screen shot. Thanks John

On Wed, Feb 11, 2015 at 12:54 AM, Celle Fetalver <

• ###### Re: Basic calculated formula question in Designer

I'll have to investigate because I am getting the same result. I'll let you know what I find. Can you attach the file again? Thanks.

• ###### Re: Basic calculated formula question in Designer

This is a small sample in Excel. You can see the comments above A and L.

Thanks

John

On Thu, Feb 12, 2015 at 1:23 AM, Celle Fetalver <

• ###### Re: Basic calculated formula question in Designer

Hi! Can you email me the sample file instead? I am not seeing the attachment anywhere.

• ###### Re: Basic calculated formula question in Designer

Celle, One approach, or work around I’m testing. Since values are aggregated by default to sum, I will ignore the field that needs to be summarized. But create a min formula by taking the field that needs to be a min value in my formula. The New calculated formula/Field is called “MinimumX” = Min(X,9999999999999999)= Minimum value which will be X regardless of the breakdown. Now use the new created field “MinimumX” for the original desired formula. Originally I wanted Min(X)/Sum(Y). The new formulas is  called “New” = “MinimumX”/Y This should give me Min(X)/Sum(Y) and the new calculated field “New” can be used in Tree maps dynamically. Let me know what you think if this makes sense. Thanks John

• ###### Re: Basic calculated formula question in Designer

Celle, the only solution is to use the pivot method you described. If you can figure out why we both got N/A for our pivot we are closer to using that work around method for the calculated field Sum(X)/Min(Y). Thanks John

• ###### Re: Basic calculated formula question in Designer

Okay John. I got the file you sent via  email. I'll use it to investigate the issue we are encountering. I'll update you as soon as I can.

Thanks,

Celle

• ###### Re: Basic calculated formula question in Designer

Hi John,

The workaround I gave you is for time series transformation, where the N/A values in the preview pane are expected. You can use a time series visualization for the time series transformed data set. If the (time series) visualization does not present or provide the information you need, you might need to prepare your data in Modeler instead.

Celle

• ###### Re: Basic calculated formula question in Designer

Okay thanks. Pivot works only for Time Series visualization not visualizations such as tree map, heat map, pie chart, etc. Data will always sum contingent on dimension selected for the break down. Min and Max function used in a calculations will take the Min or Max for the SUM of the measure at the breakdown selected. Thanks John