topic How to compare 2 dimensions in expression in QlikView

How to compare 2 dimensions in expression

VENKY_A — Thu, 11 Nov 2021 16:21:03 GMT

I have table like this.

Market	Market Unit	sales
Europe	Europe	100
Europe	UK	10
Europe	Iberia	20
Europe	Gallia	30
America	North	30
America	South	40
America	America	120

I have two buttons Market and Market Unit.

When ever Market is selected, In a chart I want to show Average of records whose Market and Market Unit is same.

Market	Market Unit	NWD
Europe	Europe	100
America	America	120

if Market Unit is selected, In a chart I want to show Average of records whose Market and Market Unit are not same.

Market	Market Unit	NWD
Europe	UK	10
Europe	Iberia	20
Europe	Gallia	30
America	North	30
America	South	40

Actually i want Overall, Market level and Market Unit Level, so i tried something like this but when ever i disable dimensionality values are not showing correct. If I keep the dimension enable it is also appearing the result/bar graph

if(Dimensionality = 2, Sum([Chargeable hours])/Avg({$<Market = {'Result'}>}NWD),
if(Dimensionality = 3, Sum([Chargeable hours])/Avg({$<Flag = {1}>}NWD),
Sum([Chargeable hours])/Avg({$<Flag = {0}>}NWD)))

Re: How to compare 2 dimensions in expression

abhijitnalekar — Fri, 12 Nov 2021 07:35:11 GMT

Hi @VENKY_A ,

Please check the attached QVW.

Let me know if you are looking the same or not.

Re: How to compare 2 dimensions in expression

MarcoWedel — Sat, 13 Nov 2021 21:35:59 GMT

One solution might be also:

tabSales: LOAD Market, [Market Unit], If(Market=[Market Unit],'Market','Market Unit') as MarketUnitSwitch, sales FROM [https://community.qlik.com/t5/QlikView-App-Dev/How-to-compare-2-dimensions-in-expression/m-p/1858306] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

Marco

Re: How to compare 2 dimensions in expression

VENKY_A — Tue, 23 Nov 2021 12:26:43 GMT

Hi Marko,

I used a similar approach before your post but that servers the purpose. thank you.

I'm maintaining flags to get my results appropriate.

if(Market = MarketUnit,2,Flag) as Flag,