16 Replies Latest reply: Aug 26, 2016 12:56 AM by Ishan Bansal

# Calculating YTD based on the dynamic year and week parameter

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.

• ###### Re: Calculating YTD based on the dynamic year and week parameter

May be like this:

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

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.

• ###### Re: Calculating YTD based on the dynamic year and week parameter

Hi,

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

what will the measure for this?

• ###### Re: Calculating YTD based on the dynamic year and week parameter

What were you using before?

• ###### Re: Calculating YTD based on the dynamic year and week parameter

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

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

or

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

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

• ###### Re: Calculating YTD based on the dynamic year and week parameter

we have week and year as a parameter.

bucketing - pie chart ( 5 different buckets) - on YTD

client making transactions based on the bucket ytd

• ###### Re: Calculating YTD based on the dynamic year and week parameter

In Detail.

This is at a YTD level.

if user is not selecting any week nor year as a parameter then default should be max week and max year

if user is selecting a week and not year then it should consider max year

if user is selecting year then it should consider max of week as default.

based on the transcations by the customer during the YTD bucketing has to be created and has to be displayed in the pie chart.

eg :

bucket 1 - 10%

bucket 2 - 50%

bucket 3 - 30%

bucket 4 - 10%

• ###### Re: Calculating YTD based on the dynamic year and week parameter

Ishan -

I am not sure how else to help brother. I have been asked few times if you can share a sample. If you are unable to share than I hope someone else may be able to offer a better advice based on the facts that you have provided above

Best,

Sunny

• ###### Re: Calculating YTD based on the dynamic year and week parameter

Hi Sunny,

Have provided all he details.

Can you let me know of any possibilities?