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

[jerryr125]

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

[Prashant_Naik]

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 

[stevedark]

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

[jerryr125]

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

[jerryr125]

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

[stevedark]

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.

[jerryr125]

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?

[Kushal_Chawda]

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

[jerryr125]

Hi Tried that as well as the following :

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

still no luck

[dwforest]

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