topic Re: chart displaying year to date vs. year to date previous year in App Development

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

jerryr125 — Sat, 04 Jan 2020 01:35:35 GMT

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

Re: chart displaying year to date vs. year to date previous year

Prashant_Naik — Sat, 04 Jan 2020 08:28:04 GMT

Hello,

This might help you,

For year 2020

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

For Year 2019

Sum({<IDATE={">=$(=AddYears(YearStart(Max(IDATE)),-1)) <=$(=Date(Addyears(Max(IDATE)),-1)))"}>}IAMOUNT) --->LYTD

please refer the link for more info --> YTD,MTD,QTD,WTD in Qlik Sense

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

Re: chart displaying year to date vs. year to date previous year

stevedark — Sun, 05 Jan 2020 15:49:58 GMT

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-with-Set-Analysis/ta-p/1495445

Hope that helps.

Steve

Re: chart displaying year to date vs. year to date previous year

jerryr125 — Mon, 06 Jan 2020 20:33:42 GMT

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

Re: chart displaying year to date vs. year to date previous year

jerryr125 — Tue, 07 Jan 2020 12:38:29 GMT

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

Re: chart displaying year to date vs. year to date previous year

stevedark — Tue, 07 Jan 2020 12:52:47 GMT

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.

Re: chart displaying year to date vs. year to date previous year

jerryr125 — Tue, 07 Jan 2020 13:44:56 GMT

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?

Re: chart displaying year to date vs. year to date previous year

Kushal_Chawda — Tue, 07 Jan 2020 14:03:46 GMT

may be this

Sum({<IDateKPI,Year,Month,IDateKPI={">=$(=date(YearStart(Max(IDateKPI)),'YYYYMMDD'))<=$(=Date(Max(IDateKPI),'YYYYMMDD'))"}>}IAMOUNT) -------> YTD Sum({<IDateKPI,Year,Month,IDateKPI={">=$(=date(YearStart(AddYears(Max(IDateKPI),-1)),'YYYYMMDD'))<=$(=date(YearEnd(AddYears(Max(IDateKPI),-1)),'YYYYMMDD'))"}>}IAMOUNT) --->LYTD

Re: chart displaying year to date vs. year to date previous year

jerryr125 — Tue, 07 Jan 2020 19:26:34 GMT

Hi Tried that as well as the following :

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

still no luck

Re: chart displaying year to date vs. year to date previous year

dwforest — Tue, 07 Jan 2020 20:09:33 GMT

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

Re: chart displaying year to date vs. year to date previous year

Kushal_Chawda — Tue, 07 Jan 2020 20:52:03 GMT

Please post sample application to look at

Re: chart displaying year to date vs. year to date previous year

jerryr125 — Tue, 07 Jan 2020 21:35:00 GMT

I got it finally - yeah! Thanks for all of your help - Jerry