Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
This expression works: =(SUM({<id_pss={'$(vSupply1)'}, year_pss={">=$(vOneFrom) <=$(vOneTo)"}>} price_pss) - SUM({<id_psd={'$(vDemand1)'},year_psd={">=$(vOneFrom) <=$(vOneTo)"}>} price_psd))
This expression works in a Table with Year as the dimension.
However, I want to know how many years did we have +ve pricing and how many years we have -ve pricing.
So I make a KPI with this measure: So I added my expression above inside this COUNT({<key={"-above expression-<0"}>} distinct key)
COUNT({<key={"=(SUM({<id_pss={'$(vSupply1)'}, year_pss={">=$(vOneFrom) <=$(vOneTo)"}>} price_pss) - SUM({<id_psd={'$(vDemand1)'},year_psd={">=$(vOneFrom) <=$(vOneTo)"}>} price_psd))<0"}>} distinct key)
This measure fails to work cause of the double quotes inside this expression: year_psd={">=$(vOneFrom) <=$(vOneTo)"}>}.
Note: The above expression works without the year filter.
It's because you have nested double quotes. Replace the inner double quotes with square brackets [ ... ] or grave accents ` ... `. Double quotes can always be replaced by either square brackets or grave accents.
See more on https://community.qlik.com/t5/Design/QlikView-Quoteology/ba-p/1476029
and https://community.qlik.com/t5/Design/Quotes-in-Set-Analysis/ba-p/1471824
Could there be a reason when I do the sum it shows me the correct number of years but as soon as I wrap it in the Count Distinct it gives me sum of 0 years. I incorporated the `` brackets as well.