Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good morning all. I am farily new to Qlikview and I am having an issue with a set analysis. I have selected a time period of 201603 which I break into two vaiables AcctPeriodYear = 2016 and AcctPeriodMonth = 3 and I wrapped both assignments with the Num(). In my set analsis it is ignoring the comparison portion and giving me all years while the month protion is working properly.
=SUM({<[Acct Period Year]={"=$(AcctPeriodYear)"}, [Acct Period Month]={"<=$(AcctPeriodMonth)"}>} [Revenue])
Can anyone possibly see why I am getting all years instead of just the year of the selected value?
I have fixed my issue and I thank you for the comments. Here is my fix
SUM({<[Acct period Year]={'$(AcctPeriodYear)'}, [Acct Period Month]={'<=$(AcctPeriodMonth)'}>} [Revenue])
I replaced teh double ticks with single ticks. I also removed the '=' infront of $(AcctPeriodYear).
Try below expression
=SUM({<[Acct Period Year]={'=$(AcctPeriodYear)'}, [Acct Period Month]={'=$(AcctPeriodMonth)'}>} [Revenue])
Are you trying to calculated Year to Date Revenue in your chart? Can you share more information as to your timeperiod field? Is it a date field or a number field?
It is a number field. To be specific, I select 201603 in a list box. From there AcctPeriodYear = NUM(Left(201603, 4)). and AcctPeriodMonth =NUM( right(201603, 2)). My expression is the one stated above. I checked the summation and it is years 2015 and 2016 and the months <= 3. Yes it is a YTD summation.
I have changed my set analysis to where it only looks at the AcctPeriodMonth and hard coded the data model so the AcctPeriodYear = 2016 and my expression was correct.
I have fixed my issue and I thank you for the comments. Here is my fix
SUM({<[Acct period Year]={'$(AcctPeriodYear)'}, [Acct Period Month]={'<=$(AcctPeriodMonth)'}>} [Revenue])
I replaced teh double ticks with single ticks. I also removed the '=' infront of $(AcctPeriodYear).
Although you have been able to get this fixed, I would seriously consider you to changing your number field (201603) into a date field. It would be useful to work with in the future
Use the expression in a Straight Table, remove the label setting and the substituted expression will be displayed in the heading. What does the expression look like after $() expansion? You should be able to right click the heading and copy to clipboard.
-Rob
The single ticks are correct when you removed the "=" sign. The second one, with "<=", should use double quotes. I know it works, but that's because the syntax is not enforced, but it may be some day.
-Rob