Creeating ratio between total count and corped count

Hello, Its 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 : 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, Im 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 doesnt 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 dont 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 Ill walk you through the process. In Celonis 4:

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

Find help.PNG545685 32.2 KB

1. Click on help and then click on manual

select manual.PNG1655813 74.2 KB

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.

finding functions in Celonis 4 documentation.PNG1879466 41.1 KB In the IBC:

1. Go to the project workspace (i.e. make sure youer 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

find help IBC.PNG1917656 107 KB

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.

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: image.png479686 13.6 KB 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.

Hi, I created an Olap table and Im trying to find the % of SO lines corresponding to each dimension. However, this function didnt work and just yields zero. Any clue as to why it wouldnt work? I used: COUNT_TABLE(VBAP) / GLOBAL (COUNT_TABLE(VBAP))