Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

If Count Expression

I need help with a formula that I've been given. 


=Aggr(

  If(count([filename]) <= 10, Dual('0-10 Times', 0),

  If(count([filename]) > 10 and count([filename]) <= 30, Dual('11-30 Times', 1),

  If(count([filename]) > 30 and count([filename]) <= 50, Dual('31-50 Times', 2),

  If(count([filename]) > 50, Dual('50+ Times', 3)))))

, [filename])



I use this formula for the following bar chart:

2018-10-17 14_22_50-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png


Now, if I make a selection and on this bar chart, for lets say, 31-50 Times, this works.  I get this result:

2018-10-17 14_24_38-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png



Problem: if I make a selection on the pie chart, for lets say, 2018, the bar chart selection changes:

2018-10-17 14_26_31-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png


The bar chart selections removed the column for 31-50 Times and is providing two other columns instead.  Instead, I would expect no columns shown on the bar chart because there are no longer any filenames in 2018 that were used 31-50 Times.

I understand why it's giving me these results - because it filtered for the "Filename" and not for "31-50 Times":2018-10-17 14_30_40-Enterprise Reports Usage - Report Usage (continued) _ Sheets - Qlik Sense.png

How can I adjust the formula to fix this problem?

Thank you!