Set Analysis With Variable

[callen973]

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?

[krishnacbe]

Try below expression

=SUM({<[Acct Period Year]={'=$(AcctPeriodYear)'}, [Acct Period Month]={'=$(AcctPeriodMonth)'}>} [Revenue])

[sunny_talwar]

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?

[callen973]

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.

[callen973]

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.

[callen973 · Accepted solution]

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).

[sunny_talwar]

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

[rwunderlich]

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

http://masterssummit.com

http://qlikviewcookbook.com

[rwunderlich]

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