# Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor

## Cumulative revenue calculation with a sum if formula

Dear community,

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

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

Tags (4)
9 Replies
Honored Contributor III

## Re: Cumulative revenue calculation with a sum if formula

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

New Contributor

## Re: Cumulative revenue calculation with a sum if formula

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

New Contributor

## Re: Cumulative revenue calculation with a sum if formula

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

Honored Contributor III

## Re: Cumulative revenue calculation with a sum if formula

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)

Honored Contributor III

## Re: Cumulative revenue calculation with a sum if formula

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)

New Contributor

## Re: Cumulative revenue calculation with a sum if formula

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]:

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]:

[Kostenstelle],

[Ber_name],

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

[Erloese_Netto],

[Belegnummer_ErloeseID],

[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]:

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

New Contributor

## Re: Cumulative revenue calculation with a sum if formula

Hi all,

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

Thanks a lot.

Gregor

Honored Contributor III

## Re: Cumulative revenue calculation with a sum if formula

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

New Contributor

## Re: Cumulative revenue calculation with a sum if formula

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