Intersection Using P() Function

[marishnagendran]

Hi All,

The above is my source data & Output data .

Continuously last 2 months if the customer was not buyed(No Sales). Then the customer and product data wants to shown in the table. 

Attached Excel sheet FYR.

Please help me out

OutPut: 

Customer	Product
Customer A	Product A
Customer A	Product B
Customer A	Product D
Customer B	Product A

Thanks,

Marish 

[vikasmahajan]

Hi ,

you can apply  following set :

Dimension : 

Customer & Product 

Measure:

Sum({<  Reason = {"No Sales"}  >} Sales)

or 

Did you try indirect set analysis P() & E() :

https://community.qlik.com/t5/QlikView-App-Dev/Indirect-Set-Analysis/td-p/1300861

https://community.qlik.com/t5/QlikView-App-Dev/P-E-functions/td-p/1346858

https://community.qlik.com/t5/QlikView-App-Dev/P-E-and-where-do-you-use-them/td-p/457847

Hope this help you.

Vikas

[tresesco]

🙂 - I liked the challenge. Thanks for asking the question here.

One of the ways could be like following.

Load *,
MonthName(Month) as MonthName
,Customer&'-'&Product as Cust_Prod
Inline [
Month, Customer, Product, Reason
45039, Customer A, Product A, No Sales
45039, Customer A, Product B, No Sales
45039, Customer A, Product C, Sales
45039, Customer A, Product D, No Sales
45039, Customer B, Product A, No Sales
45039, Customer B, Product B, Sales
45069, Customer A, Product A, No Sales
45069, Customer A, Product B, No Sales
45069, Customer A, Product C, No Sales
45069, Customer A, Product D, No Sales
45069, Customer B, Product A, No Sales
45069, Customer B, Product B, Sales
]

Get the latest two months and use them to find the intersection data set for them.

Only({
<Cust_Prod=p({<Reason={'No Sales'},Month={'$(=Num(AddMonths(Max(Month),-1)))'}>})>
*
<Cust_Prod=p({<Reason={'No Sales'},Month={$(=Max(Month))}>})>
}
Product
)