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:19:17 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:23:46 GMT

I don't have QS installed. Can you try this way?

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

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

bhargav_bhat — Thu, 11 Jan 2018 11:52:00 GMT

stalwar1‌‌

Hi Sunny ,

Can you please provide some suggestion to resolve this issue

Regards,

Bhargav

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

sunny_talwar — Thu, 11 Jan 2018 12:13:37 GMT

So when you select 3 in productID, what exactly do you want to see?

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

bhargav_bhat — Thu, 11 Jan 2018 13:02:55 GMT

Hi Sunny,

There will be an input box where the user will set the no of Previous IDs to be displayed,

For example if the user sets the value as 12 and he selects ProductID as 15 then in the data grid values from 3 to 15 should be displayed along with measures

If you observe in the screenshot, if we select productid as 3 then all previous ProductID measure values are showing null

Can you help resolve this issue

Regards,

Bhargav

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

sunny_talwar — Fri, 12 Jan 2018 09:24:47 GMT

May be this

Sum({<ProductID = {"$(='>=' & (Max(ProductID) - Variable) & '<=' & Max(ProductID))"}>}Amount)

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

bhargav_bhat — Fri, 12 Jan 2018 09:38:08 GMT

Hi Sunny,

I tried that expression but I am getting null values for previous Product IDs

In below example I have selected ProductID as 3

Regards,

Bhargav

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

sunny_talwar — Fri, 12 Jan 2018 11:38:53 GMT

Why do you have Pick(Dim,...) in your expression... would you be able to share a sample to see what you might be doing?

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

bhargav_bhat — Fri, 12 Jan 2018 11:47:33 GMT

Hi Sunny ,

We are using Pick(Dim, ProductID, 'Variance') as Dimension because we want measures in columns along with the Dimension.In Qliksense we cannot keep measures in rows and columns at the same time so to overcome this limitation we are using Pick(Dim.ProductID,'Variance' ) .

Regards,

Bhargav

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

sunny_talwar — Fri, 12 Jan 2018 11:51:34 GMT

Would you be able to share a sample