topic Re: Last Quarter same day and Last year same day in QlikView

Last Quarter same day and Last year same day

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

Hi ,

I need to calculate sales for current quarter vs last quarter . Problem is it has to be calculated for the same day as today for last quarter.

For example : if today is 9-19-2018 Q3 then it should show sum of sales for 6-19-2018 Q2.

Similarly i need to do that for Last Year .

Any idea how this can be done ?

Thanks

Re: Last Quarter same day and Last year same day

dplr-rn — Thu, 20 Sep 2018 17:48:36 GMT

Not knowing your data model its difficult give an accurate solution. hopefully below can nudge you in right direction

depending on your data model create measures

Sum({<Date="$(=today())">}

Sum({<Date="$(=AddMonths(today(),-3))">}

Sum({<Date="$(=AddYear(today(),-1))">}

Sum({<Date="$(=AddYear(AddMonths(today(),-3),-1))">}

Re: Last Quarter same day and Last year same day

MK9885 — Thu, 20 Sep 2018 17:57:26 GMT

Maybe create flags for previous year and previous quarter in back end first

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

and in front end use

Previous Quarter:

sum({< Week = {">=$(=Max(Week(Date)))"}, LastQtrFlag ={1} >} YOURMEASUREHERE )

Previous Year:

sum({< Week = {">=$(=Max(Week(Date)))"}, LastYearFlag={1} >} YOURMEASUREHERE )

Not sure if this would work but you need to have Week, Date, Year, Month and Quarter field (derive it in Master Calendar script or if using only fact table define in fact)