Aggregating and Calculating Dimension

[mrthomasshelby]

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'.

stalwar1‌ Please help me out.

TIA!

[#2]

Hello Phalgun,

Find the file attached,

Let me know if it worked,

Rima

[mrthomasshelby]

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!

[sunny_talwar]

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

[mrthomasshelby]

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!

[sunny_talwar · Accepted solution]

May be this as your dimension

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

[mrthomasshelby]

Thanks. This was exactly what I wanted!