Creeating ratio between total count and corped count

Dear All,

We are having a count of Sales Orders as a KPI. Then we are creating an OLAP table with orders coming from Quote and non Quote. Now we want to add a percentage / Ratio in OLAP table. When I try to add the COUNT(DISTINCT(“VBAK”.VBELN)) to the ratio, it does’t give me the correct result. How can I achieve the same? Thanks

Hello,

It’s not clear to me what your trying to achieve. What is the Percentage / Ratio supposed to show? Is it the ratio of Sales orders with Quotes and the sales orders without quotes? Or is it the percentage of total sales orders which have a Quote?

Could you please tell us the code for all the dimensions and KPIs in your table?

Best wishes,

Calandra

Hi Calandra,

Here are the information.

We have one single Key Figure : COUNT(DISTINCT(“VBAK”.VBELN))

We have OLAP Table which tells how many SO are from Quotes and not from Quotes

Formula which we have for numbers are : COUNT_TABLE(“VBAK”) based on Quote selection where we have a case statement :

CASE

WHEN ISNULL(“VBAK”.“ZZQUOTATION_ID”) = 1 THEN ‘no Quote’

WHEN “VBAK”.“ZZQUOTATION_ID” = ‘’ THEN ‘no Quote’

ELSE ‘with Quote’ END

Now what we are looking for is another field which gives how many % of orders are coming from Quotes or non Quotes like
below :

image002.jpg

Formula which we are looking is : Orders coming from Quotes / Total number of Orders as % and vice versa. What is the
option to do this in OLAP table.

Thanks !

Hi,

I’m very sorry about the late reply, I was on holiday last week.

The function your looking for is GLOBAL(). It is aggregated over all whole columns and so doesn’t split cases used in the KPI by dimension. It is affected by filters and selections like other functions.

The code for the '% of orders coming from Quotes or non-quotes’ is:

COUNT_TABLE(“VBAP”) / GLOBAL(COUNT_TABLE(“VBAP”))

It will give you the percentage of cases belonging to each row as determined by the dimension.

Does this solve your problem?

Best wishes,

Calandra

Hi Calandra,

That resolved my problem. Is there a way to look for these functions. Cause in PQL reference in code editor, I don’t see for e.g. Global available.

Thanks for support. Regards

Naveen Gupta

Hi,

GLOBAL() and other more advanced functions are documented in the help pages as they are too complicated to adequately explain in the PQL reference. It can be a little tricky to find the help pages so I’ll walk you through the process.

In Celonis 4:

  1. Go to the start page and click on your name to open the drop down:

  1. Click on ‘help’ and then click on ‘manual’

  1. Once in the manual, search for “PQL Function Library”.

  2. In the PQL Functions Library you can either search the name of functions you want to know more about or click on functions to open the menu of available functions.

In the IBC:

  1. Go to the project workspace (i.e. make sure you’er not currently in an analysis).

  2. Click on the ‘C’ Icon in the corner to open the drop down and then click on ‘Help resources’

  1. In the IBC documentation either type a function name into the search bar. Or open the functions selection by clicking on “Process Query Language (PQL)”, then on “PQL Function Library” and then on “Functions”.

finding%20functions%20in%20IBC%20documentation

I hope this helps.

Best wishes,

Calandra

Hi,

to add to what Calandra wrote: In the IBC, the function is actually already contained in the (reworked) PQL reference:

In fact, now every existing operator is documented there. You can open the corresponding help page by simply clicking the “Help” link.
This will also be the case in the upcoming CPM4.6 release.

Best regards
David

Thanks @c.eckert and @d.becher for the helpful information.