topic Re: How to Calculate YTD With different start date in QlikView

How to Calculate YTD With different start date

udit_kumar_sana — Thu, 26 Dec 2013 07:11:47 GMT

Hi all,

Pls guide ,how to calculate YTD for dimensions with different start date.

Pls find the sample qvw file & Screenshot for the same.

Thanks ,

Udit

Re: How to Calculate YTD With different start date

jagan — Thu, 26 Dec 2013 07:25:35 GMT

Hi,

Use this expressions, you need to Max() to get Max date, otherwise it will give you null value, because there are many dates

sum({<CalendarDate={">=$(=date(addyears(Max([Store Opening Date]), 1)))<=$(=max(CalendarDate))"}>}Sales)/100000

sum({<CalendarDate={">=$(=date(((Max[Store Opening Date]))))<=$(=addyears(max(CalendarDate),-1))"}

>}Sales)/100000

Regards,

Jagan.

Re: How to Calculate YTD With different start date

udit_kumar_sana — Thu, 26 Dec 2013 07:35:08 GMT

This expression gives me for the max storeopening date only.

I want ytd for all stores starting from its own store opening Date .

Re: How to Calculate YTD With different start date

jagan — Thu, 26 Dec 2013 07:37:40 GMT

Hi,

Try Min() instead of Max().

Regards,

Jagan.

Re: How to Calculate YTD With different start date

udit_kumar_sana — Thu, 26 Dec 2013 07:43:05 GMT

Min Or Max will give me for one store only at a time.

I want YTD for all Stores having different opening date.

Re: How to Calculate YTD With different start date

jagan — Thu, 26 Dec 2013 08:06:58 GMT

Hi,

Try this

=Sum(Aggr(If(CalendarDate >= date(addyears(Min([Store Opening Date]), 1)) AND CalendarDate <= Date(max( TOTAL CalendarDate)), Sum(Sales)), [Store No], CalendarDate))/100000

Regards,

Jagan.

Re: How to Calculate YTD With different start date

Gysbert_Wassenaar — Thu, 26 Dec 2013 09:47:42 GMT

Set analysis creates one set per chart, not a set per row. So what you want can't efficiently be done with set analysis. You can either use an if statement or change the script to calculate a flag field and use that new flag field in a set analysis expression. See attached qvw.