Display Last Month Selected Only in Straight Table

[#1]

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

[thomaslg_wq · Accepted solution]

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

[#3]

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