topic Re: Aggregating and Calculating Dimension in App Development

Aggregating and Calculating Dimension

mrthomasshelby — Fri, 16 Jun 2017 13:19:59 GMT

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!

Re: Aggregating and Calculating Dimension

Fri, 16 Jun 2017 13:53:35 GMT

Hello Phalgun,

Find the file attached,

Let me know if it worked,

Rima

Re: Aggregating and Calculating Dimension

mrthomasshelby — Fri, 16 Jun 2017 13:57:09 GMT

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

sunny_talwar — Fri, 16 Jun 2017 14:00:25 GMT

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

Re: Aggregating and Calculating Dimension

mrthomasshelby — Fri, 16 Jun 2017 14:11:24 GMT

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

sunny_talwar — Fri, 16 Jun 2017 15:10:02 GMT

May be this as your dimension

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

Re: Aggregating and Calculating Dimension

mrthomasshelby — Mon, 19 Jun 2017 06:17:47 GMT

Thanks. This was exactly what I wanted!