topic Re: P() set analysis with multiple conditions in QlikView

P() set analysis with multiple conditions

michaelsikora — Tue, 31 Jul 2018 21:19:26 GMT

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

Re: P() set analysis with multiple conditions

vishsaggi — Tue, 31 Jul 2018 21:37:00 GMT

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)

Re: P() set analysis with multiple conditions

sunny_talwar — Wed, 01 Aug 2018 01:38:03 GMT

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)

Re: P() set analysis with multiple conditions

michaelsikora — Wed, 01 Aug 2018 12:15:39 GMT

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

=sum(

{

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

}

NetSalesUSD)

Re: P() set analysis with multiple conditions

michaelsikora — Wed, 01 Aug 2018 19:55:55 GMT

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