Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mrthomasshelby
Contributor II

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


stalwar1‌ Please help me out.


TIA!

1 Solution

Accepted Solutions

Re: Aggregating and Calculating Dimension

May be this as your dimension

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

6 Replies
Not applicable

Re: Aggregating and Calculating Dimension

Hello Phalgun,

Find the file attached,

Let me know if it worked,

Rima

mrthomasshelby
Contributor II

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

mrthomasshelby
Contributor II

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:

 

CodeVar1Var2Var3
1000393344000
1000393344000
10003933440.0051120.0051120.005112
1000393344000
10003933440.1513440.1513440.151344
1000393344000
1000393344000

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)

mrthomasshelby
Contributor II

Re: Aggregating and Calculating Dimension

Thanks. This was exactly what I wanted!

Community Browser