Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

data_RN
New Contributor II

How can I remove a dimension from a table when it is part of a calculated expression?

Hello Again, 

I have been trying this all morning, and figured I would put it out there as there has got to be an easy answer that clearly won't just come to me 🙂

Essentially I am trying to build a table that will only show the total number of programs is with a certain assessment that is done .  The end goal is to have a 'heat-map' like display highlighting groups that have a low-level of completion.

I am looking at the current stage of a program and including the program if the assessment for that stage is done.   I can get it to display properly if I display the stage in the table, but once I remove the dimension from my table, the calculation isn't working.

I also tried to just hide the dimension but the list keeps the rows for that dimension and I want it to be a simple (uncollapsed) view similar to this:

GroupList Done Count
 5
a1
b3
c1

 

As a reference, here is my complete table for the example above:

GroupProgram #StageGo-To Seed List DoneGo-To-Alpha List DoneGo-To-Beta List DoneCount (Program)List Done Count
      90
a2AlphaYesNoYes10
a1SeedYesYesYes11
a3SeedNoYesYes10
b4BetaYesNoYes11
b6AlphaYesYesYes11
b5SeedYesYesYes11
c7BetaYesNoNo10
c8BetaYesNoNo10
c9AlphaYesYesNo11

Here is the formula I am using for 'List Done Counts':

(If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]),
If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]),
If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0))))

Attached is the dashboard in case that is easier.

Thanks in advance for any guidance!

1 Solution

Accepted Solutions

Re: How can I remove a dimension from a table when it is part of a calculated expression?

Try using this

RangeSum(
Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])
)
4 Replies

Re: How can I remove a dimension from a table when it is part of a calculated expression?

Try using this

RangeSum(
Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program]),
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program]),
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])
)
data_RN
New Contributor II

Re: How can I remove a dimension from a table when it is part of a calculated expression?

Of course it works just as I was hoping for!

Thank you!

Just curious, is there an easy explanation as to why RangeSum doesn't need for the the dimension to be in the table versus the IF statements needing it to be?

Re: How can I remove a dimension from a table when it is part of a calculated expression?

You don't really need the RangeSum() here... this would work the same way

Count({<[Go-To Seed List Done] = {'Yes'}, [Stage] = {'Seed'}>}[Program])
+
Count({<[Go-To-Alpha List Done] = {'Yes'}, [Stage] = {'Alpha'}>}[Program])
+
Count({<[Go-To-Beta List Done] = {'Yes'}, [Stage] = {'Beta'}>}[Program])

But I like to use RangeSum() because it can handle null better than the plus (+) sign. For example RangeSum(10 + Null()) = 10, but 10 + Null() = Null().

The change I made is to convert If statement to set analysis... if statement didn't work because there were multiple stages for each group. When you said Stage = ...., if worked when stage was a dimension, but when stage is not a dimension.... the chart has multiple stages and nulls out because it doesn't know what exactly to use. To fix this, you can use Aggr() function with Group and Stage as dimension

Sum(Aggr(
(If([Stage]='Seed',Count({<[Go-To Seed List Done] = {'Yes'}>}[Program]),
If([Stage]='Alpha',Count({<[Go-To-Alpha List Done] = {'Yes'}>}[Program]),
If([Stage]='Beta',Count({<[Go-To-Beta List Done] = {'Yes'}>}[Program]),0)))),
Group, Stage))

But why complicate things, when we can simplify them

data_RN
New Contributor II

Re: How can I remove a dimension from a table when it is part of a calculated expression?

Agreed!  and that makes total sense.

Thanks again for your help!

Cheers!