How to add indirect set analysis to an expression

[#1]

Good day,

I´m trying to add indirect set analysis to an expression, the expression looks like this and works fine:

Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} >} Sales)

I would like it to take into consideration only stores that had sales in the previous years to get a Like-For-Like comparison, and tried something like this:

Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"[StoreName]=P(=InYearToDate(DateKey, Today(), -1)[StoreName])",

"[StoreName]=P(=InYearToDate(DateKey, Today(), -2)[StoreName])"} >} Sales)

But this does not seem to be correct, does anyone have a suggestion on how to add the P() to this expression?

[effinty2112]

Hi Gudmundur,

I've not able to test this of course so I apologise for syntax errors but I think you should take your set modifier that works and take the intersection of that with the set of stores that have had sales in the last two years. Something like this:

<DateKey = {"=InYearToDate(DateKey, Today(), 0)","=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} > //Your set modifier

*

<StoreName = P({<DateKey = {"InYearToDate(DateKey, Today(), -1)"}> + <DateKey = {"InYearToDate(DateKey, Today(), -2)"}>})> // Additional set modifier

Good luck

[#3]

Thank you Andrew for your response.

I tried what you suggested, but I´m not getting the syntax correct, but here is how I made the expression, note that this is taking 3 years into account:

Sum({<DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"}>} Sales) *

<[StoreName]=P({< Sum({< DateKey = {"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"} >} Sales)

Regards

[effinty2112]

Hello Gudmundur,

Without being able to try this out I can't guarantee the syntax is correct but let's try it. I've added notes that I hope help out.

good luck again!

Sum({

<DateKey = {"=InYearToDate(DateKey, Today(), 0)","=InYearToDate(DateKey, Today(), -1)","=InYearToDate(DateKey, Today(), -2)"}>

*

< // open angle bracket to begin new set modifier

[StoreName]=

P({< // open brackets for P expression. Always take form P({< ... >})

DateKey = {

"=InYearToDate(DateKey, Today(), 0)",

"=InYearToDate(DateKey, Today(), -1)",

"=InYearToDate(DateKey, Today(), -2)"}

>}) // close brackets for P expression.

> // closed angle bracket to end new set modifier

}Sales)

[#5]

Hi Andrew,

I tried this and I get OK on the syntax but it is not working, I still get all stores, not just those that have sales in both or all selected years.

sum({<DateKey={"=InYearToDate(DateKey, Today(), 0)", "=InYearToDate(DateKey, Today(), -1)", "=InYearToDate(DateKey, Today(), -2)"}>

*

<[StoreName]=P({<DateKey={"=InYearToDate(DateKey, Today(), 0)", "=InYearToDate(DateKey, Today(), -1)", "=InYearToDate(DateKey, Today(), -2)"}>})>} Sales)

If you have any more ideas that would be great, thanks again for your help!

Best regards

Gudmundur.