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 doest give me the correct result. How can I achieve the same? Thanks
Page 1 / 1
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
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 !
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
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
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:
Find help.PNG545685 32.2 KB
select manual.PNG1655813 74.2 KB
finding functions in Celonis 4 documentation.PNG1879466 41.1 KB
In the IBC:
find help IBC.PNG1917656 107 KB
I hope this helps.
Best wishes,
Calandra
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:
- Go to the start page and click on your name to open the drop down:
Find help.PNG545685 32.2 KB
- Click on help and then click on manual
select manual.PNG1655813 74.2 KB
Once in the manual, search for PQL Function Library.
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:
Go to the project workspace (i.e. make sure youer not currently in an analysis).
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
- 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
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))
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))
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.