topic Re: Only show values until selected date in QlikView

Only show values until selected date

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Only show values until selected date

rubenmarin — Tue, 26 Jul 2016 07:38:19 GMT

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.

Re: Only show values until selected date

Tue, 26 Jul 2016 07:40:59 GMT

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

Re: Only show values until selected date

rubenmarin — Tue, 26 Jul 2016 07:43:34 GMT

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

...

Re: Only show values until selected date

Tue, 26 Jul 2016 07:46:10 GMT

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

Re: Only show values until selected date

rubenmarin — Tue, 26 Jul 2016 07:46:21 GMT

Can you try if this works?:

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

Re: Only show values until selected date

Wed, 27 Jul 2016 10:31:33 GMT

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!!

Re: Only show values until selected date

rubenmarin — Wed, 27 Jul 2016 11:00:32 GMT

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)

Re: Only show values until selected date

Wed, 27 Jul 2016 11:16:29 GMT

You're a legend

Works fine!! Thanks!