Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please post sample application to look at
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
Hi,
You might want to take a look at the prior period example I uploaded to the Community, here:
Hope that helps.
Steve
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
Hi - any luck/thoughts on the above ? I tried a couple of things - no luck.
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.
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?
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
Hi Tried that as well as the following :
Sum({$<[IDate.autoCalendar.Year] = {"$(vPriorYear)"}, [IDate] = {"$<=(vPriorYearDate))"}>} [IAmount])
still no luck
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