10 Replies Latest reply: Dec 5, 2017 11:17 AM by Gregor Voigt RSS

    Cumulative revenue calculation with a sum if formula

    Gregor Voigt

      Dear community,

       

      I'm currently struggling with the calculation of a quite simple KPI.

       

      Task:

      The KPI should display the sum of revenues of the YTD based on the filtered month.

       

      Example:

      If I filter April, the result should be the sum of revenues from January to April (April included). In this case, all revenues from May to December of that year are supposed to be excluded.

      If no filter is applied, the sum of revenue should be from January until today.

       

      If the second condition is too complex, I could also imagine skipping it and setting the KPI equal to 0 if no filter was applied.

       

      Can you support me with the KPI formula creation?

       

      Thank you and regards,

       

      Gregor

        • Re: Cumulative revenue calculation with a sum if formula
          Shraddha Gajare

          If you have DateField in your Data Model (Format is DD-MM-YYYY) then

           

          Sum({<DateField = {">=$(=Date(YearStart(max(DateField)),'DD-MM-YYYY'))<=$(=Date(Max(DateField),'DD-MM-YYYY'))"},MonthField=,YearField=>}Revenue)

           

           

          You can change Date Format according to your data model

            • Re: Cumulative revenue calculation with a sum if formula
              Gregor Voigt

              Hi Shraddha,

               

              thank you very much for your response. I think it is very close to the final solution but still not correctly working.

              I now used the following one:

               

              =Sum({<date = {">=$(=Date(YearStart(max(date)),'MM.YYYY'))<=$(=Date(Max(date),'MM.YYYY'))"},MonthField=,YearField=>}Erloese_Netto)

               

              My date field is "date" ,  my date format is "MM.YYYY", my revenue column is called "Erloese_Netto" and I do not have any month field or year field.

               

              As a result, without selection the KPI outputs the whole some of the revenues of the current year and by selecting e.g. "June" as target month, it only displays the revenue of June instead of the revenue sum of January to June.

               

              Where's the error?

               

              Thanks a lot.

               

              Gregor

                • Re: Cumulative revenue calculation with a sum if formula
                  Gregor Voigt

                  Additional comment:

                   

                  Using "month(date)" and "year(date)" instead of monthfield / yearfield brings that the KPI result is always equal to zero

                  • Re: Cumulative revenue calculation with a sum if formula
                    Shraddha Gajare

                    can you share a sample app?

                     

                    Else

                     

                    In Load Script modify your date field as

                     

                    Date#(date,'MM.YYYY') as date

                     

                    now try using

                     

                    Sum({<date = {">=$(=Date(YearStart(max(date)),'MM.YYYY'))<=$(=Date(Max(date),'MM.YYYY'))"}>}Erloese_Netto)

                      • Re: Cumulative revenue calculation with a sum if formula
                        Shraddha Gajare

                        also you can try

                         

                        Sum({<date = {">=$(=Date(YearStart(max(Date#(date,'MM.YYYY'))),'MM.YYYY'))<=$(=Date(Max(Date#(date,'MM.YYYY')),'MM.YYYY'))"}>}Erloese_Netto)

                          • Re: Cumulative revenue calculation with a sum if formula
                            Gregor Voigt

                            Hi Shraddha,

                             

                            I'm afraid to tell you that it still does not work. The formulas do not show any errors but if I select e.g. June I just receive the Revenue of June instead of the sum of revenues from January to June.

                            Initially I thought about creating a flag which basically is the number of the month and create a conditional sum that sums over all months smaller or equal to the selected one. Unfortunately I did not succeed.

                             

                            This is my load script which you requested:

                             

                            Set dataManagerTables = '','Target_Rev_Vol','Tabelle1','Sheet0';

                            //This block renames script tables from non generated section which conflict with the names of managed tables

                             

                             

                            For each name in $(dataManagerTables)

                                Let index = 0;

                                Let currentName = name;

                                Let tableNumber = TableNumber(name);

                                Let matches = 0;

                                Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

                                    index = index + 1;

                                    currentName = name & '-' & index;

                                    tableNumber = TableNumber(currentName)

                                    matches = Match('$(currentName)', $(dataManagerTables));

                                Loop

                                If index > 0 then

                                        Rename Table '$(name)' to '$(currentName)';

                                EndIf;

                            Next;

                            Set dataManagerTables = ;

                             

                             

                             

                             

                            Unqualify *;

                             

                             

                            [Target_Rev_Vol]:

                            LOAD [Kostenstelle],

                            Date(Date#("Date", 'YYYY-MM'),'MM.YYYY') as date,

                            [Target_rev],

                            [Target_vol]

                            FROM [lib://Test2/171123_BusCase2017_Target Rev Vol.xlsx]

                            (ooxml, embedded labels, table is Target_Rev_Vol);

                             

                             

                            [Tabelle1]:

                            LOAD

                            [Kostenstelle],

                            [Ber_name],

                            Date(Date#("LeistungsDatumMonat", 'YYYY-MM'),'MM.YYYY') as date,

                            [Erloese_Netto],

                            [Belegnummer_ErloeseID],

                            [Business_partner_Erloese_ID],

                            [Differenzrechnung],

                            [Erloese_Artikel],

                            [Erloese_Menge],

                                Num(month("LeistungsDatumMonat")) as month_number

                            FROM [lib://Test2/171109_Input_Umsatz.xlsx]

                            (ooxml, embedded labels, table is Tabelle1);

                             

                             

                            [Sheet0]:

                            LOAD

                            [Dienstleister],

                            [Kostenstelle],

                            [Department],

                            [Account],

                            [Program],

                            [Projekt],

                            [Anzahl Geräte],

                            [Kalkulierter Wert],

                            Date(Date#("Datum", 'YYYY-MM'),'MM.YYYY') as date

                             

                             

                             

                             

                            FROM [lib://Test2/171109_Input_First Scan.xlsx]

                            (ooxml, embedded labels, header is 3 lines, table is Sheet0);

                            • Re: Cumulative revenue calculation with a sum if formula
                              Shraddha Gajare

                              At the end of this expression you can add Month= in set analysis.

                      • Re: Cumulative revenue calculation with a sum if formula
                        Gregor Voigt

                        Hi all,

                         

                        unfortunately this problem has not been solved yet. Is there somebody that can help me out with it?

                         

                        Thanks a lot.

                         

                        Gregor