topic Re: Expression for calculating YTD in QlikView

Expression for calculating YTD

Fri, 09 Oct 2015 08:09:31 GMT

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

Re: Expression for calculating YTD

Gysbert_Wassenaar — Fri, 09 Oct 2015 14:37:58 GMT

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

Re: Expression for calculating YTD

miguelbraga — Fri, 09 Oct 2015 14:44:30 GMT

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

Re: Expression for calculating YTD

Gysbert_Wassenaar — Fri, 09 Oct 2015 14:47:18 GMT

Thank you for replicating my answer... incorrectly.

Have a nice weekend

Re: Expression for calculating YTD

Sat, 10 Oct 2015 06:26:14 GMT

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.

Re: Expression for calculating YTD

jagan — Sat, 10 Oct 2015 07:17:49 GMT

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.

Re: Expression for calculating YTD

Gysbert_Wassenaar — Sat, 10 Oct 2015 07:18:38 GMT

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?

Re: Expression for calculating YTD

Sat, 10 Oct 2015 07:26:15 GMT

Hi Jagan,

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

Thanks

Re: Expression for calculating YTD

Sat, 10 Oct 2015 07:29:19 GMT

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??

Re: Expression for calculating YTD

Gysbert_Wassenaar — Sat, 10 Oct 2015 07:33:54 GMT

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)

Re: Expression for calculating YTD

Sat, 10 Oct 2015 07:43:05 GMT

Gysbert,

I so much interpreted it wrongly. Thanks for clearing things! And am so sorry about causing so much confusion. Also, the previous expression works in that case whereas, this one gives data onwards the selected date. Both usable according to requirements.

Many Thanks!

Re: Expression for calculating YTD

jagan — Sat, 10 Oct 2015 08:32:27 GMT

Try like this

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

Regards,

Jagan.