

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
P() set analysis with multiple conditions
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the response. Unfortunately, this returns tot sum of max year net sales. Similar to the statement below:
=sum(
{
<Year={$(=Max(Year))}>
}
NetSalesUSD)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sunny - this is exactly the answer I was looking for. Thank you very much!
