topic Re: Getting Previous N Product IDs data in Pivot table with calculated dimension in QlikView

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

bhargav_bhat — Thu, 11 Jan 2018 07:21:44 GMT

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

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

Anil_Babu_Samineni — Thu, 11 Jan 2018 07:25:53 GMT

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

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

bhargav_bhat — Thu, 11 Jan 2018 07:27:53 GMT

Hi Anil,

Thanks for replying but thats not working

And its a different issue

Regards,

Bhargav

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

Anil_Babu_Samineni — Thu, 11 Jan 2018 07:31:09 GMT

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')