topic Re: Prior YTD in QlikView

Prior YTD

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

Hello,

I am struggling to get my Prior YTD function working:

Sum({<Date={">=$(=Num(YearStart(Max(Date))-365))<=$(=Max(Date)-365)"}>} [Billing Quantity])
/Sum({<Date={">=$(=Num(YearStart(Max(Date))-365))<=$(=Max(Date)-365)"}>} #Units)

Please let me know what you think!

Re: Prior YTD

kamielrajaram — Thu, 10 May 2018 21:02:51 GMT

Hi,

You can set 2 variables

vPreStartDate = Date(YearStart(Max(addmonths(Date,-12))))

vPreEndDate = Date(addmonths(Max(Date),-12))

and make your expression as

Sum({<Date={">=$(vPreStartDate )<=$(vPreEndDate )"}>} [Billing Quantity]

Sum({<Date={">=$(vPreStartDate )<=$(vPreEndDate )"}>} [#Units])

Regards

Kamiel

Re: Prior YTD

Anil_Babu_Samineni — Fri, 11 May 2018 01:57:32 GMT

You can try this? If Date field is in number format, Then below works

Sum({<Date={">=$(=Num(AddYears(Max(Date)),-1))<=$(=Max(Date))"}>} [Billing Quantity])
/

Sum({<Date={">=$(=Num(AddYears(Max(Date)),-1))<=$(=Max(Date))"}>} #Units)

If Date format is in Date Format, Then this?

Sum({<Date={">=$(=Date(AddYears(Max(Date)),-1))<=$(=Max(Date))"}>} [Billing Quantity])
/

Sum({<Date={">=$(=Date(AddYears(Max(Date)),-1))<=$(=Max(Date))"}>} #Units)

Re: Prior YTD

shiveshsingh — Fri, 11 May 2018 04:22:36 GMT

May be this

Sum({<Date={">=$(=Date(AddYears(Max(Date),-1)))<=$(=Max(Date))"}>} [Billing Quantity])
/

Sum({<Date={">=$(=Date(AddYears(Max(Date),-1)))<=$(=Max(Date))"}>} #Units)

Re: Prior YTD

Anonymous — Fri, 11 May 2018 05:08:30 GMT

May be this

=Sum({<Date={">=$(=YearStart(Max(Date),-1))<=$(=Date(Max(Date)),-365)"}>} [Billing Quantity])/

=Sum({<Date={">=$(=YearStart(Max(Date),-1))<=$(=Date(Max(Date)),-365)"}>} #Units)

Re: Prior YTD

sasiparupudi1 — Fri, 11 May 2018 08:40:10 GMT

May be try

Sum({<Date={">=$(=YearStart(Max(Date),-1))<=$(=AddYears(Max(Date),-1))"}>} [Billing Quantity])

/Sum({<Date={">=$(=YearStart(Max(Date),-1))<=$(=AddYears(Max(Date),-1))"}>} #Units)

Re: Prior YTD

pawwy1415 — Fri, 11 May 2018 11:18:51 GMT

Hi,

Try this one.This should work.

Sum({<Date={">=$(=Date(YearStart(max(Date-1),'MM/DD/YYYY')) <=$(=Date(AddYears(max(Date),-1),'MM/DD/YYYY'))"}>} [Billing Quantity])

/ Sum({<Date={">=$(=Date(YearStart(max(Date-1),'MM/DD/YYYY')) <=$(=Date(AddYears(max(Date),-1),'MM/DD/YYYY'))"}>} #Units)