topic Re: Calculating months intervals in QlikView

Calculating months intervals

Tue, 17 Jun 2014 10:19:11 GMT

Hi ALL,

i need calculate sales from some special date intervals...

SUM TY

sum of all 12 months from last MonthName, in my expamle last MonthName is sep2013, so i need calculate sep2013, aug 2013, jul 2013, jun2013........till sep 2012

SUM LY

the same as last sum of all 12, start from last MonthName but previos year , sum sep 2012, aug 2012, jul 2012 .....till sep 2011.

please help.

Re: Calculating months intervals

Tue, 17 Jun 2014 12:19:41 GMT

ok, i find how to set max and min date variables, but dont know how to write set analysis:

this works fine : sum({$<MonthYear={'$(vSUMTYmaxdate)'}>}[Sales Volume 1000 Kgs/Ltrs/Pcs])

shows sales for sep2013

sum({$<MonthYear={'$(vSUMTYmindate)'}>}[Sales Volume 1000 Kgs/Ltrs/Pcs])

shows sales for oct2012

how write set analysis for interval sum?

sum({$<MonthYear={'>=$(vMATTYmindate)<=$(vMATTYmaxdate)'}>}[Sales Volume 1000 Kgs/Ltrs/Pcs])

dosnt work

Re: Calculating months intervals

richard_pearce6 — Tue, 17 Jun 2014 12:23:17 GMT

This post shows how to create a calendar with flags to handle such requirements

http://community.qlik.com/docs/DOC-6593

Regards

Richard

QlikCentral.Com

Re: Calculating months intervals

Tue, 17 Jun 2014 12:54:35 GMT

thanks, but i need correct set analysis, i cant modifi load script...

Re: Calculating months intervals

richard_pearce6 — Tue, 17 Jun 2014 13:02:56 GMT

This post may help then

http://community.qlik.com/docs/DOC-6163

Re: Calculating months intervals

Tue, 17 Jun 2014 13:58:34 GMT

thanks, but i dont know how do this:

'First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date'

Re: Calculating months intervals

richard_pearce6 — Tue, 17 Jun 2014 14:41:25 GMT

Must admit I'm having a few issues with this because of how the data has been loaded. Very easy when you're working with numbers. Not so much when you have 'Sep 2012' etc

Here's a not so good looking work around. I'd suggest sorting the data out though

sum({<MonthYear={
'$(=date(addmonths(vMATTYmaxdate,-12),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-11),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-10),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-9),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-8),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-7),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-6),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-5),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-4),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-3),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-2),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-1),'MMM YYYY'))'
,'$(=date(addmonths(vMATTYmaxdate,-0),'MMM YYYY'))'

}>}[Sales Volume 1000 Kgs/Ltrs/Pcs])

Regards

Richard

Re: Calculating months intervals

Wed, 18 Jun 2014 07:05:34 GMT

Thanks !!! works great.

If imposible, i want to calculate Year to date.. in may case - sum from Jan 2013 till Sep 2013. How can i made this?

Re: Calculating months intervals

richard_pearce6 — Wed, 18 Jun 2014 07:57:06 GMT

Again this is a struggle because of the data you have. Something like:

SET vThisYear = '=date(YearStart( max(MonthYear) ),'MMM YYYY')';

sum({<MonthYear={
'$(=date(addmonths(vThisYear,+0),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+2),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+3),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+4),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+5),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+6),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+7),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+8),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+9),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+10),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+11),'MMM YYYY'))'
,'$(=date(addmonths(vThisYear,+12),'MMM YYYY'))'

}>}[Sales Volume 1000 Kgs/Ltrs/Pcs])

Regards

Richard

Ps - To help others can you mark the thread as answered if your original requirements have been met, thanks

Re: Calculating months intervals

Wed, 18 Jun 2014 10:07:37 GMT

thanks!!

but i resolve this little different, i think is correct to

set variable preveios year: vPreviousYear = Year(Today())-1

set variable max month vMonth = num(Month(Today()))

and set looks like this:

sum({$<Year={$(vPreviousYear)},Month={'<=$(=vMonth)'}>} [Sales Volume 1000 Kgs/Ltrs/Pcs])

Thanks for all, and good day.