Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bhargav_bhat
Creator II
Creator II

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

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

Pivot.PNG

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

9 Replies
Anil_Babu_Samineni

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
bhargav_bhat
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

Capture.PNG

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
Creator II
Creator II
Author

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