Creating KPI for Debtor / Creditor Days - Page 2 - Qlik Community

Report Inappropriate Content · ‎2010-08-11

<body>Hello, I am having problems with creating KPI's - I know how to extract the data from individual tables separately but am struggling to include them altogether in one chart due to joins or selections cancelling each other out because of different tables being used. See below for details. Any help would be much appreciated. Thanks, Conor Creditor Days Calculation: All invoices paid within 30 days in a given period ---------------------------------------------------------------------- All invoices paid + Open invoices greater than 30 days old in a given period Logic: Example: January 2010 <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext;" class="MsoTableGrid"> <tbody> <tr style="mso-yfti-irow: 0;"> <td width="139" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; width: 104.4pt; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; border: windowtext 1pt solid;"> Desc </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;"> Code </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;"> No.of documents (BELNR) </td> </tr> <tr style="mso-yfti-irow: 1;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> All invoices paid </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAK-AUGDT = */01/2010 (or BSAK-CreditorClosedClearingMonthYear = Apr/2010) BSAK-BLART = CF,CI,KR,RE </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 3466 </td> </tr> <tr style="mso-yfti-irow: 2;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> All invoices paid within 30 days </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAK-AUGDT = */01/2010 BSAK-BLART = CF,CI,KR,RE BSAK-AUGDT - BSEG-REINDAT between 1 and 30 incl </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 2689 </td> </tr> <tr style="mso-yfti-irow: 3; mso-yfti-lastrow: yes;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> Open Invoices > 30 days </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAK-AUGDT > 31/01/2010 BSAK-BUDAT <= <st1:date year="2010" day="31" month="1">31/01/2010</st1:date> BSAK-BLART = CF,CI,KR,RE + BSIK-BLART = CF,CI,KR,RE BSIK-BUDAT <= <st1:date year="2010" day="31" month="1">31/01/2010</st1:date> = - No.of invoices where <st1:date year="2010" day="31" month="1">31/01/2010</st1:date> – BSIK-BLDAT < 31 = </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 1214 + 37 = 1251 - 440 = 811 </td> </tr> </tbody> </table> 2689 / (3466 + 811) = 62.87% Debtor Days Calculation: All debt collected within 30 days in a given period ---------------------------------------------------------------------- All debt collected + Open debt greater than 30 days old in a given period Logic: Example: February 2010 <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse; mso-border-alt: solid windowtext .5pt; mso-yfti-tbllook: 480; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-border-insideh: .5pt solid windowtext; mso-border-insidev: .5pt solid windowtext;" class="MsoTableGrid"> <tbody> <tr style="mso-yfti-irow: 0;"> <td width="139" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; width: 104.4pt; padding-top: 0cm; mso-border-alt: solid windowtext .5pt; border: windowtext 1pt solid;"> Desc </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;"> Code </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: windowtext 1pt solid; padding-left: 5.4pt; background: #ccffff; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt;"> No.of documents (BELNR) </td> </tr> <tr style="mso-yfti-irow: 1;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> All debt collected </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAD-AUGDT = */02/2010 BSAD-BLART = RR,RV,DR </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 2649 </td> </tr> <tr style="mso-yfti-irow: 2;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> All debt collected within 30 days </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAD-AUGDT = */02/2010 BSAD-BLART = RR,RV,DR BSAD-AUGDT – BSAD-BLDAT between 1 and 30 incl </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 1735 </td> </tr> <tr style="mso-yfti-irow: 3; mso-yfti-lastrow: yes;"> <td width="139" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: windowtext 1pt solid; width: 104.4pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> Open debt > 30 days </td> <td width="385" valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; width: 289.05pt; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> BSAD-AUGDT > 28/02/2010 BSAD-BUDAT <= <st1:date year="2010" day="28" month="2">28/02/2010</st1:date> BSAD-BLART = RR,RV,DR + BSID-BLART = RR,RV,DR BSID-BUDAT <= <st1:date year="2010" day="28" month="2">28/02/2010</st1:date> = - No.of debt where <st1:date year="2010" day="28" month="2">28/02/2010</st1:date> – BSID-BLDAT < 31 = </td> <td valign="top" style="border-right: windowtext 1pt solid; padding-right: 5.4pt; border-top: #ece9d8; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #ece9d8; padding-top: 0cm; border-bottom: windowtext 1pt solid; background-color: transparent; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt;"> 3190 + 1132 =4322 - 2194 =2128 </td> </tr> </tbody> </table> 1735 / (2649 + 2128) = 36.32% </body>

suniljain · ‎2010-12-14

Dear Lenka,

It is not about the data extraction but how to build the logic on specific requirement. you know SAP have strong architecture in terms of data flow.

Regards

Sunil;