variable doesn't work in formula

Wed, 25 Nov 2020 16:16:04 GMT

Hey

I have a problem using a variable in a formula. In my qvw I want to compare 2 years with each other. I added 2 tables each with all the years without a conncection to any other table in the data model. These are used only for set analysis. As you can see the tables are not linked. In the blue box we find the formula of =money(sum({<Year={'$(vYearSelection2)'}>}_Amount))

The variable vYearSelection2 contains the following formula: =if(GetSelectedCount(YearSelection2)>0,GetFieldSelections(YearSelection2),max(YearSelection2))

This is used to select a year. I also have this for another year so that we can compare those two.

However if I compare the two years I only want the sales of months that have sales in both periods. So I first created a variable to check which year has the least amount of months with sales in it :

=if(count({<Year={'$(vYearSelection1)'},_Amount={'>0'}>}Month)>count({<Year={'$(vYearSelection2)'},_Amount={'>0'}>}Month),'$(vYearSelection2)','$(vYearSelection1)')

Then I used this variable in another variable to store all of the year's months

concat(distinct {<Year={'$(vWorstYear)'}>}chr(39) & Month & chr(39),',')

And now I want to use this variable in a set analysis expression to only show the sale of months that are stored in this variable

So the real problem is : I got my months outputted in the format i need e.g : 'apr','feb','jan'

But I can't use them in the formula

The year with the least amount of months with sales in it is reffered to as 'Worst' Month

Thx!

Re: variable doesn't work in formula

tresB — Tue, 09 May 2017 12:57:11 GMT

Try putting '=' before your variable definition. =Concat(...

Re: variable doesn't work in formula

Tue, 09 May 2017 13:40:19 GMT

Hey

Thank you for your reply, I tried that aswell but for me it doesn't seem to work

grt

topic Re: variable doesn't work in formula in QlikView

variable doesn't work in formula

Re: variable doesn't work in formula

Re: variable doesn't work in formula