P() set analysis with multiple conditions

[michaelsikora]

Hello All,

I am trying to create an expression that uses set analysis to show sales where a couple of different metrics need to be taken into consideration. The expression below shows me the net sales for unique product ID's that had sales last year.

=sum({<

Year={$(=Max(Year))},

ProdId = p({<Year={$(=max(Year)-1)},CurPrevYTD={1}>} ProdId)

>} NetSalesUSD)

However, I want to take into consideration the warehouse that the product is coming out of. I thought that the following expression might work.

=sum({<

Year={$(=Max(Year))},

ProdId = p({<Year={$(=max(Year)-1)},CurPrevYTD={1}>} ProdId),

Whse_Name = p({<Year={$(=max(Year)-1)}>} Whse_Name)

>} NetSalesUSD)

The problem is that the P() for ProdId and P() for Whse_Name are looked at separately. Any ideas on how to make the set analysis take both ProdId and Whse_name into consideration together?

Much Thanks,

Michael

[vishsaggi]

May be try below:

=sum({<

Year={$(=Max(Year))},

ProdId = p({<Year={$(=max(Year)-1)},CurPrevYTD={1}>} ProdId) >+<

Whse_Name = p({<Year={$(=max(Year)-1)}>} Whse_Name)

>} NetSalesUSD)

[sunny_talwar · Accepted solution]

May be create a new field in the script like this

LOAD Prodid & Whse_Name as ProdWhseKey

and then this

=Sum({<Year = {$(=Max(Year))}, ProdWhseKey = p({<Year = {$(=Max(Year)-1)}, CurPrevYTD = {1}>} ProdId)>} NetSalesUSD)

[michaelsikora]

Thanks for the response. Unfortunately, this returns tot sum of max year net sales. Similar to the statement below:

=sum(

{

<Year={$(=Max(Year))}>

}

NetSalesUSD)

[michaelsikora]

Sunny - this is exactly the answer I was looking for. Thank you very much!