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]

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

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

[bhargav_bhat]

stalwar1‌‌

Hi Sunny ,

Can you please provide some suggestion to resolve this issue

Regards,

Bhargav

[sunny_talwar]

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

[bhargav_bhat]

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

[sunny_talwar]

May be this

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

[bhargav_bhat]

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

[sunny_talwar]

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

[bhargav_bhat]

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

[sunny_talwar]

Would you be able to share a sample