Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

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
Kush
MVP
MVP

Please post sample application to look at

View solution in original post

11 Replies
Prashant_Naik
Partner
Partner

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
Luminary Alumni
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

jerryr125
Professor-Ambassador
Professor-Ambassador
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

 

 

jerryr125
Professor-Ambassador
Professor-Ambassador
Author

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

stevedark
Luminary Alumni
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.

jerryr125
Professor-Ambassador
Professor-Ambassador
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?

 

 

Kush
MVP
MVP

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
Professor-Ambassador
Professor-Ambassador
Author

Hi Tried that as well as the following :

 

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

 

still no luck

dwforest
Specialist II
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