Getting Previous N Product IDs data in Pivot table with calculated dimension

[bhargav_bhat]

Hi All,

we have a requirement where the user wants previous 'N'  no of ProductID when he selects a particular productID. He will provide the value of N using variable extension.

This we were able to achieve if the dimension is only Product ID but if we use calculated dimension as Pick(Dim, ProductID, 'Variance') then we get previous ProductID values as null as shown below

we tried adding Previous ProductID is set analysis

Sum({<ProductID={1,2,3,4,5}>}Amount)

Also we tried ignoring the PRoductID filter

Sum({<ProductID=>}Amount)

but we were getting null values

Is there any workaround to achieve this

I have attached sample app for reference

Regards,

Bhargav

[Anil_Babu_Samineni]

Looks like you've updated. Perhaps try this?

Pick(Dim, Sum({<ProductID={1,2,3,4,5}>}Amount))

don't create duplicate https://community.qlik.com/message/1415444

[bhargav_bhat]

Hi Anil,

Thanks for replying but thats not working 

And its a different issue

Regards,

Bhargav

[Anil_Babu_Samineni]

What this Variance calculated? Normally, When we are working rule from island table it should be calculate 2 measures. Which is

Pick(Dim, Sum({<ProductID={1,2,3,4,5}>}Amount), 'Here Variance statement')