Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
thanks, but i need correct set analysis, i cant modifi load script...
This post may help then
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'
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
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?
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
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.