Cumulative revenue calculation with a sum if formula

[Anonymous]

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

[shraddha_g]

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

[Anonymous]

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

[Anonymous]

Additional comment:

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

[shraddha_g]

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)

[shraddha_g]

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)

[Anonymous]

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);

[Anonymous]

Hi all,

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

Thanks a lot.

Gregor

[shraddha_g]

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

[Anonymous]

I can't follow your advise. Can you specify what you meant?