topic Re: Dynamic Dimension or Expression based on GetPossibleCount in QlikView

Dynamic Dimension or Expression based on GetPossibleCount

whiteymcaces — Mon, 26 Feb 2018 13:41:04 GMT

I have a major App where I have a chart that I want analysis on Stock Items that are on Special.

Specials are created in the source data, sometimes there is a single item, and sometimes there are multiple items. When there are multiple items, usually they are the same type of item, but just different colours.

I want to create a chart that has 1 row per Special, per Region. There are 6 Regions. If there is only 1 Special, then the chart will only have 6 rows. If there are 2 Specials, then the chart will have 12 rows etc.

If the Special only has 1 Stock Item then I want the data in the cell under the Heading 'Barcode' to be the actual Barcode, however, if the Special has more than 1 Stock Item, then I want the data in the cell under the Heading "Barcode' to be hard coded to the word 'Various'.

If there are multiple Specials in the same chart, then the ones with 1 Stock Item should have the Barcode, and the ones with more than 1 Stock Item should have 'Various'. All in the same chart.

This can be a Calculated Dimension or and Expression - whichever works.

Re: Dynamic Dimension or Expression based on GetPossibleCount

sunny_talwar — Mon, 26 Feb 2018 13:47:37 GMT

Something like this

Dimension

Region

Expressions

If(Count({<hasSpecial = {'1'}>}Barcode) > 1, 'Various', Barcode)

Sum({<hasSpecial = {'1'}>}Amount)

Re: Dynamic Dimension or Expression based on GetPossibleCount

whiteymcaces — Mon, 26 Feb 2018 13:51:32 GMT

What happens with no selections? I need all Specials in the chart.

Re: Dynamic Dimension or Expression based on GetPossibleCount

sunny_talwar — Mon, 26 Feb 2018 13:54:44 GMT

I see this without selections

Selection was made to show that if there is only one Barcode, it will show up as the Barcode number rather than Various

Re: Dynamic Dimension or Expression based on GetPossibleCount

whiteymcaces — Mon, 26 Feb 2018 14:00:42 GMT

There should be up to 4 rows per Region, 1 for each Special. Maximum 24 Row, based on Sales. If the Special has only 1 Stock Item, it should show the Barcode. If the Special has more than 1 Stock Item it should show 'Various' but the Sales should be for all Stock Items within that Special.

Re: Dynamic Dimension or Expression based on GetPossibleCount

sunny_talwar — Mon, 26 Feb 2018 14:03:05 GMT

Like this?

Re: Dynamic Dimension or Expression based on GetPossibleCount

whiteymcaces — Mon, 26 Feb 2018 14:06:03 GMT

Yes, but I'm not doing a pivot table, and I don't want to see the Special Dimension.

Re: Dynamic Dimension or Expression based on GetPossibleCount

sunny_talwar — Mon, 26 Feb 2018 14:11:07 GMT

This?

Re: Dynamic Dimension or Expression based on GetPossibleCount

whiteymcaces — Mon, 26 Feb 2018 14:13:35 GMT

That's looking good. What's the Expression?

Re: Dynamic Dimension or Expression based on GetPossibleCount

sunny_talwar — Mon, 26 Feb 2018 14:18:12 GMT

Dimension

=Dual(Region, Aggr(Rank(TOTAL Special&Region), Region, Special))

Expression

If(Count({<hasSpecial = {'1'}>}Barcode) > 1, 'Various', Barcode)

Sum({<hasSpecial = {'1'}>}Amount)

I have also attached the sample qvw with my previous response