Calculating YTD based on the dynamic year and week parameter

[ishanbansal1204]

Hi All,

I want to calculate ytd value for the client registered. i have filters on week and year.

my expression is

IF (Aggr(Sum({ 1 <WEEK={"<=$(=Max(WEEK))"}>}COUNT_CLIENT),[CLIENT]) = 1, '1 Transaction')

the above expression is working fine for week. but i need to add year condition as well. how shall i do that?

what if the user select only week and not year so default it should take max year available in the filter.

[sunny_talwar]

May be like this:

If(Aggr(Sum({1<WEEK={"<=$(=Max({<Year = {$(=Max(Year))}>}WEEK))"}, Year = {$(=Max(Year))}>}COUNT_CLIENT),[CLIENT]) = 1, '1 Transaction')

[ishanbansal1204]

Hi,

This expression is considering max of year always.

scenario 1: if user selects a week and not year then max of year should be considered.

scenario 2 : if user does not select week and year then default it should consider max of year and in that year max of week

scenario 3 : if user selects year and week then ytd should be calculated based on that year and till that week.

currently if i am selecting 2015 but still i am getting data for 2016

[sunny_talwar]

That cannot be my friend. Can you share a sample with me where it is doing this? Max(Year) will change based on your selection in Year field.

[ishanbansal1204]

Hi,

Can you please suggest. year parameter is creating a problem here. its not accepting the selection.

[ishanbansal1204]

what will the measure for this?

[sunny_talwar]

What were you using before?

[ishanbansal1204]

Count({ 1 <WEEK={"<=$(=Max(WEEK))"}>} DISTINCT CLIENTNUMBERS)

[sunny_talwar]

How about this:

Count({ 1 <WEEK={"<=$(=Max(WEEK))"}, Year = $::Year>} DISTINCT CLIENTNUMBERS)

or

Count({ 1 <WEEK={"<=$(=Max(WEEK))"}, Year = p(Year)>} DISTINCT CLIENTNUMBERS)

[ishanbansal1204]

Same thing.. week is working.. year is taken as max of year available.