topic Re: Exclude Dimension Complicated in QlikView

Exclude Dimension Complicated

Wed, 25 Nov 2020 16:16:04 GMT

Hello All,

I have a pivot table which have 4 dimension

Account_Name

Rebate Type - Inline Table

Quarter

Condition- 4 different Dimensions combined via if on Rebate Type.

I wanted to exclude Branch_Name wise and Kc_Code wise selection.

In 1st Image only Account_name is selected thats why AK is green but when I select some Branchname or KCcode it turns red you can see that in 2nd image.

Second Image

I have used this expression so far to bypass Branch_name and Kc_Code but it seems not working

if(Rebate_Type='Base Rebate',if(sum({<Branch_Name=,KC_Code=>}[Base Rebate Final]),''),

if(Rebate_Type='Growth Rebate',if(sum({<Branch_Name=,KC_Code=>}[Growth Rebate Final]),''),

if(Rebate_Type='KPI Rebate',if(sum({<Branch_Name=,KC_Code=>}[KPI Rebate Final]),''),

if(Rebate_Type='SC Rebate',if(sum({<Branch_Name=,KC_Code=>}[SC Rebate Final]),''),

if(Rebate_Type='CI Rebate',if(sum({<Branch_Name=,KC_Code=>}[CI Rebate Final]),''))))))

Any upgradtion on above ?

Re: Exclude Dimension Complicated

vinieme12 — Fri, 08 Sep 2017 08:47:15 GMT

You don't have any condition the second if ?? You aren't evaluating any condition there

IF(Rebate_Type='Base Rebate',

if(sum({<Branch_Name=,KC_Code=>}[Base Rebate Final]) > , < , >= , <= some value ,''),

Re: Exclude Dimension Complicated

vinieme12 — Fri, 08 Sep 2017 08:49:47 GMT

Maybe you need just this

Pick(Match(Rebate_Type,'Base Rebate','Growth Rebate','KPI Rebate','SC Rebate','CI Rebate')

,sum({<Branch_Name=,KC_Code=>}[Base Rebate Final])

,sum({<Branch_Name=,KC_Code=>}[Growth Rebate Final])

,sum({<Branch_Name=,KC_Code=>}[KPI Rebate Final])

,sum({<Branch_Name=,KC_Code=>}[SC Rebate Final])

,sum({<Branch_Name=,KC_Code=>}[CI Rebate Final])

)

Re: Exclude Dimension Complicated

Fri, 08 Sep 2017 08:51:32 GMT

Hello Vineeth,

It just has to calculate the rebate value and I do not want to see that as its a heat map so I have given '',

Also If you have any other approach please tell me.

Re: Exclude Dimension Complicated

Kushal_Chawda — Fri, 08 Sep 2017 08:55:07 GMT

try this

Pick(match(only({<Branch_Name=,KC_Code=>}Rebate_Type),'Base Rebate','Growth Rebate','KPI Rebate','SC Rebate','CI Rebate'),

sum({<Branch_Name=,KC_Code=>}[Base Rebate Final]),

sum({<Branch_Name=,KC_Code=>}[Growth Rebate Final]),

sum({<Branch_Name=,KC_Code=>}[KPI Rebate Final]),

sum({<Branch_Name=,KC_Code=>}[SC Rebate Final]),

sum({<Branch_Name=,KC_Code=>}[CI Rebate Final]))))))

Re: Exclude Dimension Complicated

Fri, 08 Sep 2017 09:02:51 GMT

Tried this, the color is still red its not bypassing branch and kccode !!!!!!!!!!

your this expression is giving me values on cells which is fine but bypassing branch and KCcode is important.

Branch is child store of an Account_Name so AK is huge while A,B,C are small retailers into it.

ABC should be excluded only whole sum of AK (AccountName) should be there.

Re: Exclude Dimension Complicated

Fri, 08 Sep 2017 09:08:21 GMT

Thanks Bro, It Worked smooth !

Re: Exclude Dimension Complicated

Kushal_Chawda — Fri, 08 Sep 2017 09:09:08 GMT

have you tried this?

Re: Exclude Dimension Complicated

Kushal_Chawda — Fri, 08 Sep 2017 09:09:54 GMT

Glad it was helpful