Ignore a dimension filter in a KPI calculation

Hi David,

I am not quire sure what exactly you want to achieve. Could you provide more detail or a screenshot potentially so we can help you with this request? How do you want to filter only the date dimension? In case you are applying a component filter, every information within that component gets filtered based on what you define.

Best

Kevin

@kevin.flesc, as far as I understood, the problem lies in the kpi, when using running_total, the resulting value is referring to the previous values (intermediate aggregations).

When filtering the dates, the target is to show only the filtered months, but to show in the kpi the total value of the aggregation (including the out filtered rows).

So far, now filtering the running_total per month starts to add with the first filtered row.

The first idea I had was to aggregate a variable(previous_sum) to the running_total, this variable will be set with the filtered out row's sum. I don't know whether this is achievable within the studio, maybe some experience celopeers with ML_workwench like @joos.buijs or @janpeter.van.d can help us.

The problem statement would be, using the change date picker as a trigger, calculate a sum(rows) of specific rows and then store the result in a variable previos_sum

What do you think?

Hi all,

Could it be that the GLOBAL operator is wat you are looking for? Please have a look at the documentation of it: https://help.celonis.cloud/help/x/yYZRAQ. It creates an aggregation without being impacted by selections.

Kind regards,

Jan-peter

Hi David,

Making the value a CONSTANT() should also be effective.

To aggregate all values from a source column to a single value, the CONSTANT

 function can be used as a target table inside a PU-function. The resulting value is a constant.

Example:

Calculate the average of the case table values using 

CONSTANT. In contrast to the regular AVG function, and like in all PU-functions, FILTERs are ignored:

PU_AVG ( CONSTANT ( ) , "caseTable"."value" )

Best,

Gabriel

Hi Kevin, Javier, Jan-peter, Gabriel

Thanks all for your suggestions.

@kevin.flesc . Our requirement is similar to the requirement in this link from Celonis describing the behaviour of RUNNING_TOTAL. The additional requirement we have is to be able to filter the date range displayed, but preserving the KPI calculation:

https://pql.eu-1.celonis.cloud/process-mining/public/105139f8-2fa6-4ef2-b2e0-c33299e3f7e7/#/frontend/documents/105139f8-2fa6-4ef2-b2e0-c33299e3f7e7/view/sheets/17480c67-60f9-461e-a1c6-fc24a70ce20f

@janpeter.van.d @gabriel.okaba11 Both good suggestions. I'll have to see if I can work out a way to adapt the approach used in the link above to use PU-functions.

@javier.donos I hadn't thought about using ML Workbench, but that may be a great fallback option. thanks.

I'll keep you all informed as to how we go. If you have any further ideas to try, please let me know.

thanks all

David

Hi @david.van.d11,

Thanks for sharing this running_total example, I didn't see it before.

Regarding the GLOBAL operator, you can just put in front of an aggregation function (SUM in this case), and that should be it (no PU-functions required). Not sure if it also works for CASE WHEN situation, but that's something to find out.

Good luck,

Jan-peter

Hi @janpeter.van.d 

I tried that approach with the GLOBAL operator earlier today. Didn't work. All column values became the same.

I'm still learning, but I think the GLOBAL operator is syntax sugar for DOMAIN with a single value resulting table. Will continue to investigate.

thanks

David

@david.van.d11 , I' am also trying and so far not successful, sill struggling😥

@david.beche12 

Hi David,

Is the linked analysis above yours? If so, would you be able to assist?

thanks

David

Bummer, I'm out of options then. Using ML Workbench would be an option, but I guess also a bit of an overkill. Also, since the data from the OLAP table is extracted, still the same issue will exists problaly, that the filters will be applied to the target calculation.

Hi @david.van.d11 

in order to ignore the filters, you need to use PU functions instead of regular aggregation.

It depends on your KPI how it will exactly look like, but you need something like this:

Dimension: PU_FIRST( DOMAIN_TABLE( ROUND_DAY("Table"."Date") ), ROUND_DAY("Table"."Date") )

KPI: RUNNING_SUM( PU_SUM( DOMAIN_TABLE( ROUND_DAY("Table"."Date") ), "Table"."Value" ), ORDER BY ( PU_FIRST( DOMAIN_TABLE( ROUND_DAY("Table"."Date") ), ROUND_DAY("Table"."Date") ) ASC ) )

In this example, the "Table"."Value" column is summed up as the KPI (marked bold), but this is depending on your actual KPI how you need to formulate this.

Note that you also have to use RUNNING_SUM instead of RUNNING_TOTAL, as RUNNING_TOTAL will again take the filters into account.

Best

David

Thanks David.

Some really interesting techniques in your solution I would never have thought of. Thanks for responding in detail. I'll let this forum know how we go.

regards

David

Hi David,

Thanks, your suggested approach worked really well.

The double date field in the domain_table is a very cool trick I would never have thought of 😀. If my understanding is correct, this creates a CROSS JOIN with all possible date combinations.

thanks

David

Hi David,

great to hear it worked!

Are you referring to PU_FIRST( DOMAIN_TABLE( ROUND_DAY("Table"."Date") ), ROUND_DAY("Table"."Date") )?

This is not calculating a Cross Join. In general, Cross Joins are not supported in PQL. What it simply does is, it retrieves the day value from the DOMAIN_TABLE (i.e. the "first" day for each day in the DOMAIN_TABLE is that day). So in fact, this does the opposite of a join - I use it to avoid any join. Because if I would use just the regular ROUND_DAY("Table"."Date") as a dimension, while using DOMAIN_TABLE in the KPI, this would introduce the join between the DOMAIN_TABLE and the "Table". Since they have a 1:N relationship, the values would "multiply", i.e. you would have the same day multiple times in your dimension, because there is no real aggregation function used. And you cannot use real aggregation functions, because they would take the filters into account ;)

Hope that helped understanding the query!

Best

David