topic Set Analysis to calculate Prior Months in QlikView

Set Analysis to calculate Prior Months

dthornburg — Tue, 01 Nov 2016 17:18:24 GMT

HI,

I am trying to create a table that shows totals for current month - current month + 6. The first bucket is everything prior to current month and the last bucket is everything greater than the last month displayed. Labeled Previous and Future. I have all the buckets working except these 2 buckets using set analysis. I attached a screen shot of the headings I am using.

Sum({$<[so_Ship_Year]={$(=vYear_Current)},[so_ship_Month]={$(=vMonth_Current)}>}[so_open_lbs])

I have calculated all the variables:

LET vToday = (Today(1));

LET vYear_Current = Year(Today());

LET vYear_Next = vYear_Current + 1;

LET vYear_Previous = vYear_Current - 1;

LET vMonth_Current = Month(Today());

LET vMonth_Previous = Month( AddMonths(Today(),-1));

LET vMonth_Next = Month( AddMonths(Today(),+1));

LET vMonth_Next_2 = Month( AddMonths(Today(),+2));

LET vMonth_Next_3 = Month( AddMonths(Today(),+3));

LET vMonth_Next_4 = Month( AddMonths(Today(),+4));

LET vMonth_Next_5 = Month( AddMonths(Today(),+5));

LET vMonth_Next_6 = Month( AddMonths(Today(),+6));

LET vMonth_Next_7 = Month( AddMonths(Today(),+7));

I cannot figure out how to use set analysis to calculate Future or Previous. Below is what I want to work logically but it does not. Any suggestions?

Sum({$<[so_Ship_Year]={$(=vYear_Current,vYear_Previous)},[so_ship_Month]<{$(=vMonth_Current)}>}[so_open_lbs])

Re: Set Analysis to calculate Prior Months

Anonymous — Tue, 01 Nov 2016 17:42:14 GMT

LET vYear_Current = Year(Today());..............2016

LET vYear_Previous = vYear_Current - 1..........2015

Following gives sales between 2015 and 2016

Sum({$<[so_Ship_Year]={'>=$(=Date(vYear_Previous, 'YYYY'))<=$(=Date(vYear_Current, 'YYYY'))'}>} [so_open_lbs])

Re: Set Analysis to calculate Prior Months

dthornburg — Tue, 01 Nov 2016 17:47:33 GMT

For this month

Previous column should equal a total form 01/01/2000 - 10/31/2016 as an example.

Future total should equal 06/01/2017 = 12/31/2099.

I believe what you suggested will give me duplicate totals.

Re: Set Analysis to calculate Prior Months

dthornburg — Wed, 02 Nov 2016 17:17:45 GMT

I defined a variable called vYear_Month_Curr = 2016-11 for example.

Added so_Ship_Year_Month to data load so that there was a Ship Date format to match. 2016-11. Thought was I could calculate everything less than that. I cannot even get the Set Analysis to work below. Any ideas?

Sum({$<[so_Ship_Year_Month]={$(=vYear_Month_Curr)}>}[so_open_lbs])

Goal is to sum so_open_lbs where so_Ship_Year_Month < vYear_Month_Curr. Possible using Set Analysis?