Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
GrantBooth
Creator
Creator

Sum YTD

I'm a little embarrassed to be asking, but I'm having some issues creating a year to date sum using Set Expression.

 

I have a field Amount that I would like to do a YTD sum, with dates ranging from 01/2021 to 12/2021 called FinPeriod. I've been scratching my head all afternoon trying to come up with the expression and it's just not coming together, could someone please point me in the right direction?

 

Cheers

10 Replies
Vegar
MVP
MVP

To use FinPeriod to achieve this I strongly recommend that you make sure these are dual values holding a numeric date value as well as your string.

If that is the case then you can do this.

Sum({<FinPeriod = {">=$(=Yearstart(today()))<=$(=today())}>}Amount)

If the field is not a dual then check If you have another filed holding a date value, maybe you have a transaction date field? If so, then you can use the setbon that field.

PrashantSangle

in script can you create new field like

date(date#(FinPeriod,'MM/YYYY')) as newFinPeriod

 

Then in you set analysis this newFinPeriod for calculating YTD. Try below expression

Sum({<newFinPeriod={">=(=Date(Yearstart(Today())))<=(=Date(Today()))"}>}Amount)

 

Regards,

Prashant Sangle

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Vegar
MVP
MVP

I and @PrashantSangle  are basically giving you the same solution, but if your field is not a dual then you could just modify that field in the script similar to what  Prashant suggests. 

 

Load

date#(FinPeriod,'MM/YYYY') as FinPeriod,

etc...

GrantBooth
Creator
Creator
Author

Thanks to both of you. The reason I'm having these issues is due to the source data, the dates are not loaded properly as dates and is causing all sorts of issues. I'm attempting to update the data, then I'm sure your solutions will work

GrantBooth
Creator
Creator
Author

That's fantastic, I did get that to work. Now to make things a little trickier, our Financial Year is actually 01/07 to 30/6, could you please let me know how to fix that?

GrantBooth
Creator
Creator
Author

That's fantastic, I did get that to work. Now to make things a little trickier, our Financial Year is actually 01/07 to 30/6, could you please let me know how to fix that?

PrashantSangle

https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeF...

 

check 3rd parameter of yearStart(). Just play with it to get your work done.

Also check addmonth() if required.

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Vegar
MVP
MVP

Take a look at tghe 3rd parameter of YearStart as  @PrashantSangle  suggests. 

Your expression will end up something like this if you want to calculate based on selected values:

=Sum({<FinPeriod = {">=$(=Yearstart(max(FinPeriod),0,7))<=$(=max(FinPeriod))"}>}Amount)  

 

Or like this if static based on todays value:

=Sum({<FinPeriod = {">=$(=Yearstart(today(),0,7))<=$(=today())"}>}Amount)  

GrantBooth
Creator
Creator
Author

So I spoke a little too soon, and I can't get the cumulative sum to work. I'm sure that the issue is because the dates aren't being imported properly, so I've tried to make things a little easier. I've represented the year PeriodYear, and each month as an integer 1-12. The month will be selected by the user.

What I'm trying to achieve is a cumulative sum based on the selection of the month and year and I simply can't do it, could I get a little help please?