Control date for a specific value in a set analysis

[robinwiman]

Hi,

I have a Pivot table that shows Sales for the last week and then sales last week compared to last year. I.e. Sales and Sales LY.

    Sum({<[Store Type] = {'Concept', 'Estore', 'Outlet','Partner','SiS'}>} Fsgexkl_EUR*_YTD_TW_TY)

I'm using above set-analysis to keep the table set to a specific selection (concept, estore, outlet, partner, SiS).

The Fsgexkl_EUR is our sales and the _YTD_TW_TY calls for the sales in This Week and This Year (YTD gets the accumulated sales for that week).

So if we have week 33, the function will retrieve the sales for week 32.

Here's my problem:

I want the [Store Type] partner and SiS to retrieve data from two weeks prior. I.e. week 31 as per the example above.

So all selections, concept, estore etc. would retrieve data from week 32, as of now, but partner and SiS would retrieve data from week 31.

Is there a way to do this?

[antoniotiman · Accepted solution]

Hi Robin,

maybe this

Sum({<[Store Type] = {'Concept', 'Estore', 'Outlet'}>} Fsgexkl_EUR*_YTD_TW_TY)+

Sum({<[Store Type] = {'Partner','SiS'}>} Fsgexkl_EUR*YourPriorWeek)

Regards,

Antonio

[robinwiman]

Thank you Antonio,

It works, but I had to setup a new Date dimension for it. But it got solved

[antoniotiman]

Yes, You need it.

Regards,

Antonio