graph depends on toggle

[Ethel]

Hello, could you please help me with a problem.

I  have a toggle 'percent/count' and a bar chart for 5 years. The formula should be the following

if(wildmatch(PercentCount,'Count') ,

sum({Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}[Count])
,
sum({ Year={">=$(=vPriorFiveYear)<=$(=vdYear)"}>}[# Count])/
sum(total <Year> {Year={">=$(=vPriorFiveYear)<=$(=vPeriodYear)"}>}[# Count]))

If I create graphs separately, than in every case I have a chart with five bars. But If I add condition "if", than the first graph in condition shows only column for the last year instead of 5 column. Any ideas why it happens and how to fix it?

Thanks a lot in advance!

[stevedark]

Hi @Ethel ,

I'm not sure why you would see this behaviour, what you are attempting seems valid.

One thing I can spot though is your set analysis seems slightly broken, with missing < characters between the { and Year.

Also, is there a reason why you switch from Count to # Count?

The wildmatch is not required here, you can just have if(PercentCount = 'Count' (I assume you are setting the variable to this?). Using equals will be slightly more efficient.

You will also need to use the num function, to get the chart to show integers or percentages. As the first part of the expression is always the same you could have something more like this:

num(
sum({<Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}[Count])
/
if(PercentCount = 'Count', 1, 
sum(total {<Year={">=$(=vPriorFiveYear)<=$(=vPeriodYear)"}>} Count)),
'#,##0' & if(PercentCount = 'Count', '', '.0%'))

In order to have the number format set by the expression you will need to ensure that the number format on the measure is set to Auto.

Hope that helps.

Steve

[Ethel]

Thank you very much!

Unfortunately It didn't change behavior.  Is there maybe a completely different way to do it? Without if?

[stevedark]

Hi @Ethel 

The recommended way (by Qlik) is to use Alternative Measures. This gives you a little drop down on the chart to swap from one to the other.

You could also have a container of two master visualisations - but these waste a bit of space.

I'm still perplexed as to why it is not working though.

Can you try a new chart with the following expression:

sum({<Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}Count)
/
sum(total {<Year={">=$(=vPriorFiveYear)<=$(=vYear)"}>} Count)

Does this give you all years? If so you can then try layering on the toggle.

In your original code you have three different variables for the end year vYear, vdYear and vPeriodYear. Why would these not all be the same variable for the end year?

Steve

[Ethel]

Thank you! I'll try!

Those as well as Count(#count)  were typos because I was trying to simplify my formula for the example purpose. 

[Ethel]

yes, it gives all years.

[stevedark]

Okay, next step is to add in the toggle:

sum({<Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}Count)
/
if(PercentCount = 'Count', 1, sum(total {<Year={">=$(=vPriorFiveYear)<=$(=vYear)"}>} Count))

Something else has just occurred to me... what is PercentCount in this instance? Is it a field in a table or a varable? If it is a field in a table it could cause confusion - I would suggest switching to a variable with a button to toggle the value.

This blog post describes toggles:
https://www.quickintelligence.co.uk/toggle-buttons-qlik-sense/

The blog post is a bit out of date now, as you can now set a colour expression to show whether a button is on or off, but it may still be interesting.

We will get there with this one!

Steve

[Ethel]

Thank you. That's a good point. I'll read this article...That's how I define PercentCount in data loader

PercentCount:
Load * Inline [
PercentCount
Percent
Count];

[stevedark]

If you want to stick with a field (because you like the look of Filter Panes for selecting) then the way you can probably stop the field causing a problem is this:

sum({<Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}Count)
/
if('$(=minstring(PercentCount))' = 'Count', 1,
sum(total {<Year={">=$(=vPriorFiveYear)<=$(=vYear)"}>} Count))

By dollar expanding the reference to the field it will not be part of the expression in the same way, and shouldn't mess with the dimensions.

I tend to prefer using variables for these kind of things anyway.

If you want to see an app where different expressions are selected from a drop down take a look at this one:
https://www.quickintelligence.co.uk/sense/gs.html

You can download the application for free and load your own data into it, details are here:
https://www.quickintelligence.co.uk/instant-qlik-sense-app/

Cheers,

Steve

[Ethel · Accepted solution]

Thank you so so so much!!!

it's working now! The only thing is % doesn't  work, it shows fraction. Is there an easy way fix for it?

num(
sum({<Year= {">=$(=vPriorFiveYear)<=$(=vYear)"}>}[Count])
/
if('$(=minstring(PercentCount))' = 'Count', 1,
sum(total {<Year={">=$(=vPriorFiveYear)<=$(=vPeriodYear)"}>} Count)),
'#,##0' & if(PercentCount = 'Count', '', '.0%'))