Expression for calculating YTD

[#1]

Hi all,

I have a calendar date data and want to calculate YTD over it. I am having highest date in it as '01-Aug-2015' so, I am considering that as my current date. I had used following expression for calculating the YTD:

'=sum({1 <CalendarDate= {">= YearStart(GetFieldSelections(CalendarDate)) <= $(=max(CalendarDate))"}>} sale)'

and if I passed a date as '01-Jun-2013', it brought me data for sale from '01-Apr-2012' i.e. the first date in my data to '01-Jun-2013' which, it should actually bring from '01-Jun-2013' to '01-Aug-2015' (highest date in my data).

So, I reversed the operators between dates in the above expression such as:

=sum({1 <CalendarDate= {"<= YearStart(GetFieldSelections(CalendarDate)) >= $(=max(CalendarDate))"}>} BSLAMC)

This gave me the data from '01-Jun-2013' to '01-Aug-2015' and I want it from '01-Jan-2013' to '01-Aug-2015'.

I don't  understand where I am going wrong.

Any help would be greatly appreciated!

Thanks,

Madhura

[Gysbert_Wassenaar · Accepted solution]

Try =sum({1 <CalendarDate= {">=$(=YearStart(max(CalendarDate)))<=$(=max(CalendarDate))"}>} sale)

[miguelbraga]

Hi Gysbert,

Try this:

=Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(max(CalendarDate)))<=$(=max(CalendarDate))’}>} Sales )

This sample code was based on this post:

https://community.qlik.com/docs/DOC-9162

Have a good weekend

[Gysbert_Wassenaar]

Thank you for replicating my answer... incorrectly.

Have a nice weekend

[#5]

Hi Gysbert,

Thanks for replying. Badly needed help in this one. I tried with your expression but, unfortunately no luck. Now it only brings data from year start of selected date to that date.

Can't really find any reason for this.

[jagan]

HI,

Try this expression to get data from '01-Jan-2013' to '01-Aug-2015'.

sum({1 <CalendarDate= {">=$(=YearStart(max(CalendarDate), -2))<=$(=max(CalendarDate))"}>} sale)

Hope this helps you.

Regards,

Jagan.

[Gysbert_Wassenaar]

Now it only brings data from year start of selected date to that date.

Uhm... as I understand it YTD stands for Year To Date, i.e. the period from the start of the year of the selected date upto the selected date. Can you explain what you think YTD should mean?

[#8]

Hi Jagan,

Tried it. Sorry but, no luck. Brings data from first date in the table to the selected date.

Thanks

[#9]

Hi Gysbert,

I thought it means the data from the selected date's year(from beginning of year) to the latest/ current date in the table.

Am I wrong??

[Gysbert_Wassenaar]

Yes, because the latest date in the table can be in another year then the year of the selected date. Everything after the start of the year of the select date is not YTD. But try this expression: sum({1 <CalendarDate= {">=$(=YearStart(max(CalendarDate)))"}>} sale)