Only show values until selected date

[#1]

Hi guys,

The chart I am trying to create is part of a much bigger report with loads of charts. For the other tables to be correct, I have to select the desired month and year (usually the current month and year). The table I am having trouble with shows multiple years (2014, 2015, 2016 (out of 2012, 2013, 2014, 2015, 2016)) and all months. I am using the following expression to achieve this (so far, so good):

=(sum({$<Year={2014,2015,2016}, Month= >}Sales))

The issue: I want to show all information for the previous years, but only the information up until the month I selected. E.g. If I select May and the Year 2016, I want everthing to be visible up until May 2016. Thus the red, crossed numbers should be gone ('-' or blank).

I made an example in Excel. I guess I need some kind of "Make date with <= selected month and selected year", but I just don't know how...

Thanks in advance guys!

[rubenmarin]

Hi, you will need a Date field (or at least a year-month field) and this can be used:

Sum({<DateField={'<=$(Max(DateField))'}, Year, Month>} Sales)

I don't see it working assigning values to Year and Month separatelly, in example if the selected year-month is 2016-July, the set analisys will ask for months <=7 in every year!, because of that you need a field that merges year and month.

It can be done with only year and month fields but you'll need an addition of set analisys, using a date or a year-month field is easier.

[#3]

Idd, that's why I thought I had to use some sort of "make date"-combination with my selected Year and Month. And then tell QV to show me everything smaller or equal to that created date.

Edit: I do not have a MonthYear-field for that type of Date-field I am using.

How should I write the set analysis using only Month and Year (separately)?

[rubenmarin]

This can be done in the same table where you load Year and month, you can create a date like:

LOAD Year,

     Month,

     MakeDate(Year, Month) as Date, // Default Day is '1'

     // Other fields

...

[#5]

Is it possible in Set Analysis/Expressions, without touching the script? (I am not allowed to change the script)

[rubenmarin]

Can you try if this works?:

Sum({<Year={'$(=Max(Year))'}, Month={'<=$(=Max(Month))'}> + <Year={'<$(=Max(Year))'}, Month=>} Sales)

[#7]

Awesome, this did work!

BUT, don't understand how I can enter the needed years in my expression. Before your suggestion I mentioned: Year={2014,2015,2016}, and this worked fine. But now I am using your expression for my Year-part, I don't know how to slide this part in and it now shows me all the years possible.

Or do I solve this in my dimenions?

Thanks in advance, nearly there!!

[rubenmarin · Accepted solution]

It will work dynamically based on selections, if the year selected is 2015, and the month is july it will show data until July-2015 (it still will show data of previous years as it's not filtered by a minium year).

If you want fixed years:

Sum({<Year={2016}, Month={'<=$(=Max(Month))'}> + <Year={2014, 2015}, Month=>} Sales)

[#9]

You're a legend

Works fine!! Thanks!