Sum YTD

[GrantBooth]

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

[Vegar]

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

[Vegar]

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]

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]

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]

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.

[Vegar]

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]

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?