Using a condition to apply a variable in set analysis

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

My QV application has a number of charts, all designed to show current year data only. However, previous year data is also loaded in via QVD to allow other charts to show specific comparisons between the two years.

The following is in my load script:

LET vCurrent_Year= Year(Today());
LET vPrevious_Year= Year(Today())-1;

And the current year charts use the following format for the set analysis:

SUM({$<Year={$(=vCurrent_Year)}>}[Calculated Total])

This works out for 11 months out of the year. However, in January, I want December data from the previous year to remain on the current year charts, as a key function is to show growth or decline from one month to the next. On February 1, it will be acceptable for December data to be removed, and the charts to show a January/February comparison.

What would be the proper way to manage this function? I've had some thoughts, but I am not sure of syntax, and/or if any of them would even work

IF statement within the set analysis? ie- IF(Month(Today)= 'Jan', set analysis to show December of the previous year and January)
IF statement as a variable? ie- IF(Month(Today)='Jan', LET vCurrent_Year= some function to add in December to the calculation)
Separate variables for vCurrent_Year and vCurrent_Year_Plus_December, and then an IF statement in the chart to choose the variable?

Side Note- I have a Master Calendar loaded in per the instructions I found in this community. However ,I have not found a purpose for it in my application. If a Master Calendar is the way to solve this issue, I'm already a step ahead

Re: Using a condition to apply a variable in set analysis

swuehl — Fri, 17 Mar 2017 17:24:34 GMT

You can use your master calendar.

Create a flag in the master calendar to flag the dates / months you want to show in your chart, maybe something like

LOAD

DateKey,

Date,

Month,

Year,

If(Year = Year(Today()) or (Month = 12 and Year = Year(Today()-1) and Month(Today())=1 ),1,0) as Flag,

....

Then use {<Flag = {1}>}

in your set analysis to filter the dates you are interested in.

Re: Using a condition to apply a variable in set analysis

jason_nicholas — Fri, 17 Mar 2017 17:45:53 GMT

I knew it had to be simple. thanks!

topic Re: Using a condition to apply a variable in set analysis in QlikView

Using a condition to apply a variable in set analysis

Re: Using a condition to apply a variable in set analysis

Re: Using a condition to apply a variable in set analysis