topic Display Last Month Selected Only in Straight Table in QlikView

Display Last Month Selected Only in Straight Table

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am fairly new to Qlik.

I have data that is basically:

Part Number

Month Number

Production Volume

Sales Volume

Inventory Volume

I have a straight table and for each Part No, I want to display the total Production Volume and Sales Volume based on my selection of Month Number(s). I also want to display the Inventory Volume, but only for the last month selected. If I select Month 1 it displays Production Volume, Sales Volume and the Inventory Volume for January which is fine.

When I select Month 1 and Month 2 it displays the Production and Sales volume total for both months which is correct. It displays the total Inventory Volume for Month 1 and Month 2 when I only want to see the Inventory Volume for Month 2.

Can anyone suggest how I should approach this?

Many thanks,

Graham

Re: Display Last Month Selected Only in Straight Table

thomaslg_wq — Wed, 21 Dec 2016 16:45:22 GMT

Hi,

You should look on the community the key word ==> "set analysis", it allows you to filter (even dynamically with selections) when creating a measure. There are a lot of documents and tutorials about it on the community. Learning Set Analysis from Scratch

For your example on the inventory volume, your formula would be :

sum( {<[Month Number]={"$(=max([Month Number]))"}>} [Inventory Volume])

Re: Display Last Month Selected Only in Straight Table

Wed, 21 Dec 2016 17:34:34 GMT

Hi,

Thanks Thomas that helped me enormously. I will read the documents later and get to better understand the Set Analysis (especially the syntax which confuses me at the moment).

I used your code:

sum( {<[Month Number]={"$(=max([Month Number]))"}>} [Inventory Volume])

I had simplified the issue a bit when I posted, but ultimately it worked and my code looked like this:

sum( {<[PROD_YEAR]={"$(=maxstring([PROD_YEAR]))"}, [PROD_MONTH_NO]={"$(=maxstring([PROD_MONTH_NO]))"}>}[INVENTORY.INVENTORY_VOLUME])

Very much appreciated.

Graham