By Month, Year over Year Comparison Bar Chart

[zach_paz]

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

[Josh_Good]

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) & '"')}>}

[Gysbert_Wassenaar]

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.

[zach_paz]

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!

[zach_paz]

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

[Gysbert_Wassenaar]

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

[Josh_Good]

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.

[zach_paz]

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!

[Tammy_Milsom]

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