Filter Table object

[BrianDH]

I have a 10 column table with some dimensions and some measures.   Globally I only want to show the rows where the below calculation is = true  .  The below Calculation is one of my measure columns.

I am looking for examples.

if(SUM(B_BH_AMOUNT_ACT +
B_BH_AMOUNT_PAY +
B_BH_AMOUNT_GLA +
B_BH_AMOUNT_GLL +
B_BH_AMOUNT_GLQ +
B_BH_AMOUNT_GLR +
B_BH_AMOUNT_GLE +
B_BH_AMOUNT_OTHER)
>
Sum(B_BH_AMOUNT_ACC), 'true', 'false')

Thanks

[BrianDH]

vSpend = if(SUM(B_BH_AMOUNT_ACT +
B_BH_AMOUNT_PAY +
B_BH_AMOUNT_GLA +
B_BH_AMOUNT_GLL +
B_BH_AMOUNT_GLQ +
B_BH_AMOUNT_GLR +
B_BH_AMOUNT_GLE +
B_BH_AMOUNT_OTHER)
>
Sum(B_BH_AMOUNT_ACC), 1, 0)

------------------------------

DIM column = if(vSpend=1, A_OPERATING_UNIT)

---- 

Am I close?

[lorenzoconforti]

This would work for measures:

Measure column = if($(vSpend)=1, A_OPERATING_UNIT)

For dimensions you need to do it differently as you are using an aggregation function. See here:

https://community.qlik.com/t5/Qlik-Design-Blog/Calculated-Dimensions/ba-p/1472813

I would be something like this (without the variable, just to show you):

dim column = 

if(aggr(

SUM(B_BH_AMOUNT_ACT +
B_BH_AMOUNT_PAY +
B_BH_AMOUNT_GLA +
B_BH_AMOUNT_GLL +
B_BH_AMOUNT_GLQ +
B_BH_AMOUNT_GLR +
B_BH_AMOUNT_GLE +
B_BH_AMOUNT_OTHER)
>
Sum(B_BH_AMOUNT_ACC), A_OPERATING_UNIT), A_OPERATING_UNIT)

[BrianDH]

I have it working for my measures but still no luck with the Dims.  

You have me on the right path i am sure.

Thanks

[lorenzoconforti]

Can you upload the dashboard? it's probably an easy fix

[BrianDH]

No idea how to do that

[BrianDH]

Team

I need to filter out all rows where the flag is FALSE

if(SUM(B_BH_AMOUNT_ACT +
B_BH_AMOUNT_GLA +
B_BH_AMOUNT_GLL +
B_BH_AMOUNT_GLQ +
B_BH_AMOUNT_GLR +
B_BH_AMOUNT_GLE +
B_BH_AMOUNT_PAY +
B_BH_AMOUNT_OTHER) >
Sum(B_BH_AMOUNT_BUD), 'TRUE', 'FALSE')

See image 5 & 6

I have a mix of Dims and Mesus columns in my table object.  I am able to change the BG color of a DIM with my code but not filter the row out.

I know there must be a way to do this.  I only want to see rows where the Calc is True.  I even tried to add a filter pane but I get an error.

Suggestions?

[Kushal_Chawda]

may be you can put the expression in Calculated dimension 'Oper Unit'

aggr(if(SUM(B_BH_AMOUNT_ACT +
B_BH_AMOUNT_GLA +
B_BH_AMOUNT_GLL +
B_BH_AMOUNT_GLQ +
B_BH_AMOUNT_GLR +
B_BH_AMOUNT_GLE +
B_BH_AMOUNT_PAY +
B_BH_AMOUNT_OTHER) >
Sum(B_BH_AMOUNT_BUD),[Oper Unit], null()),[Oper Unit])

Now, check the option 'suppress Null value' for this calculated dimension

[lorenzoconforti]

to filters the rows out, you need to apply the condition to the measures and make sure you you deselect "Include null values" in the dimension as well as deselect "Include zero values" in add-ons>data handling

if you are using qlik sense desktop, you can find the dashboard under Documents\Qlik\Sense\Apps

[BrianDH]

OK, yes it kind of works.  No errors but not showing all my OUs.  I'll be back 😉

[BrianDH]

I am using the Web Based copy.  Yes Not showing Null / Zeros.