How to Calculate YTD With different start date

[udit_kumar_sana]

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

[jagan]

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.

[udit_kumar_sana]

This expression gives me for the max storeopening date only.

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

[jagan]

Hi,

Try Min() instead of Max().

Regards,

Jagan.

[udit_kumar_sana]

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

I want YTD  for all Stores having different opening date.

[jagan]

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.

[Gysbert_Wassenaar]

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.