topic Re: Calculate sum for last year in QlikView

Calculate sum for last year

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

I have the following expression to calculate

Date is in format YYYYMMDD

Sum({$<Date = {">=$(=vStartDate)<=$(=vEndDate)"}>} Target)

How to calculate the same but for the last year

I try Sum({$<Date = {">=$(=vStartDate)-1<=$(=vEndDate)-1"}>} Target)

but it doesn(t work how to correct it ?

sunny_talwar — Mon, 21 Mar 2016 13:03:50 GMT

May be this:

Sum({$<Date = {">=$(=Date(AddYears(vStartDate, -1), 'YYYYMMDD'))<=$(=Date(AddYears(vEndDate, -1), 'YYYYMMDD'))"}>} Target)

Digvijay_Singh — Mon, 21 Mar 2016 13:05:01 GMT

Something like this -

Sum({<Year=, Quarter=, Month=, Week=, Date={">=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))"}>} Sales)

ali_hijazi — Mon, 21 Mar 2016 13:05:54 GMT

is your date field interpreted as number in qlik if so then you need to do the following:

create two variables vStartLastYear and vEndLasYear

vStartLastYear would be = numDaystart(addYears(date($(vStartDate)),-1))

vEndLastYear would be = numDaystart(addYears(date($(vEndDate)),-1))

in your expression you would use these new variables and you ignore selections on date parts (year=, month=,etc...)