topic Re: Set Analysis With Variable in QlikView

Set Analysis With Variable

callen973 — Tue, 05 Jul 2016 14:12:07 GMT

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?

Re: Set Analysis With Variable

krishnacbe — Tue, 05 Jul 2016 14:18:21 GMT

Try below expression

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

Re: Set Analysis With Variable

sunny_talwar — Tue, 05 Jul 2016 14:25:05 GMT

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?

Re: Set Analysis With Variable

callen973 — Tue, 05 Jul 2016 14:40:15 GMT

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.

Re: Set Analysis With Variable

callen973 — Tue, 05 Jul 2016 14:41:47 GMT

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.

Re: Set Analysis With Variable

callen973 — Tue, 05 Jul 2016 15:09:39 GMT

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

Re: Set Analysis With Variable

sunny_talwar — Tue, 05 Jul 2016 15:23:08 GMT

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

Re: Set Analysis With Variable

rwunderlich — Tue, 05 Jul 2016 15:43:07 GMT

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

Re: Set Analysis With Variable

rwunderlich — Tue, 05 Jul 2016 15:44:55 GMT

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