KPI for count cases with and without the selection

Hello together,

is it possible to built an KPI in an OLAP, like:

= count cases (with sheet selection) / count cases in sum (without the sheet selections)

Thank you in advance

Sarah

Hi Sarah,
yes this is possible by using a static variable. This type of variable will always return the same value, no matter of what filters you have set in the sheet selection. In order to set this typ of variable up, please go to variables in the settings, create a new variable and select for the variable type “Static value” instead of “Text/Replacement”. You will then see the already known PQL console where you can type in your commands, e.g. COUNT_TABLE(table_of_interest). By referencing this variable in your calculation you will get the desired result. Please keep in mind, that with this type of variable also all other filters such as component filters or sheet load-scripts will be ignored as well.

Best regards,
Viana

Hello Viana,

thank you for your proposal.

I forgot to say, that the “count cases in sum” should work in an OLAP table.
So I want to get the counts of cases for every customer (this is my dimension). And when I set a static variable with COUNT_TABLE… it shows me only one sum of cases at all, for every customer (see yellow mark)

I added this screenshot to demonstrate my aim:
image

See point 1: the variable should have the figure that is directly to the left of it (at the moment the OLAP is with ignored selections, so that the sum of cases appears)

See point 2: I want also add the case sum of the figures from the left column chart (so that I can cancel the option, that selections should be ignored in the table)

I really dont know what I’m doing wrong with the static variable?

Thank you in advance

Best regards,
Sarah

Does anyone maybe have a suggestion for that?
Unfortunately, the correct use of static variables has not been conclusive to me yet.

Hi Sarah,

not sure if I understood your problem correctly. But maybe you could use PU functions, as they do not respect Analysis/Sheet/Component/Selection Filters. So you could calculate the “count cases in sum” per customer like this:

PU_COUNT_DISTINCT("CustomerTable", "CaseTable".CaseColumn")

Is that what you are looking for?

Best,
David