6 Replies Latest reply: Jun 19, 2017 2:17 AM by Phalgun P

# Aggregating and Calculating Dimension

Hello,

I have the sample data loaded in the following qvf. What I'd like to do is create a calculated dimension according to the following logic:

Aggregate the data by the field 'Code'

1.If variable=1, then in aggregated data, If Var1>0 even for one record, give the output as 'Yes', otherwise 'No'.

2.If variable=2, then in aggregated data, If Var2>0 even for one record, give the output as 'Yes',otherwise 'No'.

3.If variable=3, then in aggregated data, If Var3>0 even for one record, give the output as 'Yes', otherwise 'No'.

What I'd like to show in the pie chart finally the distinct count of Codes that fall under 'Yes' and 'No'.

TIA!

• ###### Re: Aggregating and Calculating Dimension

Hello Phalgun,

Find the file attached,

Let me know if it worked,

Rima

• ###### Re: Aggregating and Calculating Dimension

Hello Rima! Thanks a lot for the response. But the thing is I want the logic to be implemented in the front end rather than the script as I want the chart to be dynamic with a number of selections I have in my dashboard. Sorry I forgot to mention that. Is there anyway you can do this in the front end? Thanks again!

• ###### Re: Aggregating and Calculating Dimension

Once again, I am not completely sure I understand the requirement and final output needed here

• ###### Re: Aggregating and Calculating Dimension

Let me see if I can make this more clear. Firstly, aggregate the data based on the field 'Code'. Now in this aggregated data, when Variable=1, if the field Var1>0 for even one record within the aggregated data, output is 'Yes',for that particular code. if not 'No'. Similarly when Variable=2 and Variable=3,consider the fields Var2 and Var3. Finally show the count of the distinct codes that fall under 'Yes' or 'No' in the pie chart.

For example,

Code 1000393344 the aggregated data is as follows:

 Code Var1 Var2 Var3 1000393344 0 0 0 1000393344 0 0 0 1000393344 0.005112 0.005112 0.005112 1000393344 0 0 0 1000393344 0.151344 0.151344 0.151344 1000393344 0 0 0 1000393344 0 0 0

So ,if Variable=1, as we can see in the field Var1, at least one record is >0. So the output for this Code is 'Yes'.

Similarly, if Variable=2, as we can see in field Var2, at least one record is >0. So the output for this Code is 'Yes'.

Thanks!

• ###### Re: Aggregating and Calculating Dimension

May be this as your dimension

=Aggr(If(Sum(\$(='Var' & Lookback)) > 0, 'Yes', 'No'), Code)

• ###### Re: Aggregating and Calculating Dimension

Thanks. This was exactly what I wanted!