Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
rtr13
New Contributor III

Dynamic Prior YTD

Hi All,

I have seen numerous questions in regards to YTD calculations, but none in regards to a dynamic YTD calculation.

I am currently using the following set analysis to pull the sum of prior YTD Charges:

=SUM({$<ContactDTS = {'>=$(=AddMonths( YearStart (Today()),-12))<=$(=AddMonths( (Today()),-12))'}>}Charges)

But, when selecting a different year, my value drops to zero. In addition, for some reason, my number isn't accurately reflecting the sum of Charges within the field. 

Has anyone ever created a Prior YTD expression that allows is dynamic to the year selected?

Thanks,

Rose

1 Solution

Accepted Solutions
Partner
Partner

Re: Dynamic Prior YTD

You can use the following to do what you are saying. I would create an additional field of your date field but store it as a number. This will make formatting matching a lot easier.

 

Keep in mind you will have to add/replace/remove other calendar date fields in here. 

Sum({<Year=, MonthYear=, Month=, Week=, Date=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Num(MonthEnd(Max(DateNum))))"}>}Charges)

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

1 Reply
Partner
Partner

Re: Dynamic Prior YTD

You can use the following to do what you are saying. I would create an additional field of your date field but store it as a number. This will make formatting matching a lot easier.

 

Keep in mind you will have to add/replace/remove other calendar date fields in here. 

Sum({<Year=, MonthYear=, Month=, Week=, Date=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Num(MonthEnd(Max(DateNum))))"}>}Charges)

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post