topic By Month, Year over Year Comparison Bar Chart in QlikView

By Month, Year over Year Comparison Bar Chart

zach_paz — Fri, 25 Oct 2013 16:18:23 GMT

Hello,

I am tying to create a simple bar chart that displays the percent change by month for 2013 vs. 2012. I am using the following expression, but the chart displays "No Data to Display"... Please help!!!

sum({<[Year]={$(vCurrentYear)},Monthnum = {"<=$(vCurrentMonth)"}>}[qualified])/sum({<[Year]={$(=(vCurrentYear)-1)},MonthNum = {"<=$(vCurrentMonth)"}>}[qualified])

Re: By Month, Year over Year Comparison Bar Chart

Josh_Good — Fri, 25 Oct 2013 16:49:23 GMT

In your expression I believe the Monthnum is returning no results because the double quotes are causing QV to look for the text string <=$(vCurrentMonth) in the Monthnum field vs searching for <=10 (for October). Try using this instead:

Monthnum = {$(='"<=' & $(vCurrentMonth) & '"')}>}

Re: By Month, Year over Year Comparison Bar Chart

Gysbert_Wassenaar — Fri, 25 Oct 2013 17:01:57 GMT

The double quotes are not the problem. More likely is the expansion of the vCurrentYear variable. Try putting in a $ character $(=$(vCurrentYear)-1)

Also, (just in case you did) don't use Year as dimension in your bar chart. That will cause a No data to display message too.

Re: By Month, Year over Year Comparison Bar Chart

zach_paz — Fri, 25 Oct 2013 18:05:36 GMT

Thank you both for your input. I tried both options and it still does not seem to work.

This is what my expression looks like with G Wassenaar's suggestion:

sum({<[Year]={$(vCurrentYear)},Monthnum = {"<=$(vCurrentMonth)"}>}[qualified])/sum({<[Year]={$(=$(vCurrentYear)-1)},MonthNum = {"<=$(vCurrentMonth)"}>}[qualified])

Any other suggestions would be much appreciated!

Re: By Month, Year over Year Comparison Bar Chart

zach_paz — Fri, 25 Oct 2013 18:07:29 GMT

Still doesn't work... I added my new expression with your suggestion to the original thread. Any other suggestions would be much appreciated!

Re: Re: By Month, Year over Year Comparison Bar Chart

Gysbert_Wassenaar — Fri, 25 Oct 2013 18:16:13 GMT

Have a look at the attached example. Perhaps you can spot the difference. Perhaps it's in one of your variables.

Re: By Month, Year over Year Comparison Bar Chart

Josh_Good — Fri, 25 Oct 2013 18:28:48 GMT

I've played around with various dollar sign, quote bracket combinations and I'm feeling good about your syntax. The issue could be in what is being returned by the variable. Try hard coding what you believe what is being returned by the variable and see if the expression works.

Re: By Month, Year over Year Comparison Bar Chart

zach_paz — Wed, 06 Nov 2013 21:39:58 GMT

I tried everyone's suggestion and even recreated it using Gysbert Wassenaar example and it still wouldn't work. It may have to do with the variables I am using.

I used a simple expression:

=sum({<Year ={'2013'}>} inquiries) / sum({<Year ={'2012'}>} inquiries) -1

Thanks for everyones help!

Re: By Month, Year over Year Comparison Bar Chart

Tammy_Milsom — Wed, 06 Nov 2013 21:44:36 GMT

Your MonthNums are not the same case, one has a lower case 'n' and the other an upper