Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

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
sunny_talwar

May be this as your dimension

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

View solution in original post

6 Replies
Not applicable

Hello Phalgun,

Find the file attached,

Let me know if it worked,

Rima

mrthomasshelby
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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!

sunny_talwar

May be this as your dimension

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

mrthomasshelby
Creator III
Creator III
Author

Thanks. This was exactly what I wanted!