# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:

## chart displaying year to date vs. year to date previous year

Hi - I am looking for the following formula:

I would like to have a chart displaying the following two bars :

Year to date sum of my amount field (say IAMOUNT)

last Year to date sum of my amount field (say IAMOUNT)

Therefore, the chart would current show the IAMOUNT for January 2020 and January 2019

Fields: IAMOUNT, IDATE

I am assuming a master measure would help in this visualization.  Thoughts?

- Jerry

1 Solution

Accepted Solutions
MVP

Please post sample application to look at

11 Replies
Partner

Hello,

For year 2020

Sum({<IDATE={">=\$(=YearStart(Max(IDATE)) <=\$(=Date(Max(IDATE))))"}>}IAMOUNT) -------> YTD

For Year 2019

Note : i have used the Qlik sense default date format. you need to check your date format and then evaluate in set analysis expression. the expression wont work if the date format is different.

Regards,

Prashant

Luminary Alumni

Hi,

You might want to take a look at the prior period example I uploaded to the Community, here:

https://community.qlik.com/t5/Qlik-Sense-Documents-Videos/Qlik-Sense-App-Prior-Period-Comparison-wit...

Hope that helps.

Steve

Author

Hi - thank you both very much  appreciate it !

Getting very close to the solution.

My IDATE format is as follows :  MMDDYYYY  (as an example 05082019)

I also have this field which provides the year [IDATE.autoCalendar.Year] (as an example 2019)

I set my

vPriorYearDate = '=date(addyears(max(IDATE),-1),' & chr(39) & 'MMDDYYYY' & chr(39) & ')';

vPriorYear = '=vMaxYear-1';

Which results in

vPriorYearDate = 01032019

VPriorYear = 2019

(the max date of IDATE less one year)

Ok those both work, so now what do I use as a formula for the master measure PRIOR YTD

(I am getting close)

thoughts ? Jerry

Author

Hi - any luck/thoughts on the above ? I tried a couple of things - no luck.

Luminary Alumni

Hi,

What expression do you have that you believe to be close?

I think you also need the start of the prior year as a variable;

vPriorYearStart = '0101' & right(vPriorYearDate, 4)

Something like this may then work:

Sum({<IDATE={">=\$(vPriorYearStart)<=\$(vPriorYearDate)"}>}IAMOUNT)

Are you confident that IDATE is a date field (i.e. does it sort correctly in a list box) or could it be a number which would sort incorrectly.

The set analysis may treat a date in that format as an integer anyway.

You may find formatting dates to YYYYMMDD works better (as they will sort and filter correctly as a date or an integer).

Good luck.

Author

Hi - thank you again for your help.

Ok - so I did some re-formatting of the dates:

SUM(IF{<[IDateKPI.autoCalendar.Year]={'\$(vPriorYear)'}>},IF(<[IDateKPI]=<{'\$(vPriorYearDate)'}>},IAmount,0),0)

IDateKPI is now in YYYYMMDD format

VPriorYearDate is now in YYYYMMDD format

Still no luck but I think we are getting closer - thoughts?

MVP

may be this

``````Sum({<IDateKPI,Year,Month,IDateKPI={">=\$(=date(YearStart(Max(IDateKPI)),'YYYYMMDD'))<=\$(=Date(Max(IDateKPI),'YYYYMMDD'))"}>}IAMOUNT) -------> YTD

Author

Hi Tried that as well as the following :

Sum({\$<[IDate.autoCalendar.Year] = {"\$(vPriorYear)"}, [IDate] = {"\$<=(vPriorYearDate))"}>} [IAmount])

still no luck

Specialist II

No quotes on a numeric set value

Sum({\$<[IDate.autoCalendar.Year] = {\$(vPriorYear)}, [IDate] = {"\$<=(vPriorYearDate))"}>} [IAmount])

You also have to make sure your date format matches the format in your script default