
Re: Basic calculated formula question in Designer
Kait Riel Feb 7, 2015 4:52 PM (in response to DataExploiter _)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
DataExploiter _ Feb 7, 2015 5:38 PM (in response to Kait Riel)Thanks for the reply, I sent to support@datawatch.com and customer support portal. John


Re: Basic calculated formula question in Designer
Celle Fetalver Feb 11, 2015 1:22 AM (in response to DataExploiter _)Hi,
You can try using the Pivot transform option via the settings of your data source to emulate Sum(X)/Min(Y).
 Assuming that you already have a workbook, click Edit Data Table.
 Click on the Settings of your data source.
 On the Transforms tab, select Pivot transform.
 Click Add Pivot..
 Select the desired measure column and your "X" for the Value column.
 Select Sum for your aggregate.
 Click Add Pivot.
 Select the desired measure column and your "Y" for the Value column.
 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
DataExploiter _ Feb 11, 2015 8:44 AM (in response to Celle Fetalver)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
Celle Fetalver Feb 16, 2015 1:21 AM (in response to DataExploiter _)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
DataExploiter _ Feb 11, 2015 9:45 PM (in response to Celle Fetalver)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
Celle Fetalver Feb 11, 2015 10:22 PM (in response to DataExploiter _)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
DataExploiter _ Feb 11, 2015 11:04 PM (in response to Celle Fetalver)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
Celle Fetalver Feb 12, 2015 12:04 AM (in response to DataExploiter _)Hi! Can you email me the sample file instead? I am not seeing the attachment anywhere.


Re: Basic calculated formula question in Designer
DataExploiter _ Feb 12, 2015 10:18 AM (in response to Celle Fetalver)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
DataExploiter _ Feb 12, 2015 9:24 PM (in response to Celle Fetalver)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
Celle Fetalver Feb 12, 2015 10:17 PM (in response to DataExploiter _)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
Celle Fetalver Feb 16, 2015 1:23 AM (in response to DataExploiter _)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
DataExploiter _ Feb 16, 2015 8:54 PM (in response to Celle Fetalver)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



