Calculate Sum based on several field values (including date range)

[gino2780]

Hello,

i'd like to calculate an expression based upon several field selections.

Let me give you a quick data example first:

Field1          Field2         Field3        Field4

blue              won            2021-01     10000

red                lost             2021-02       2000

yellow          draw          2021-03      1500

green           won            2021-01     30000

red                lost             2021-04          900

blue             draw           2021-02       8000

Now, i need a command for e.g. KPI on a Dashboard like this:

Sum = if (Field1= 'red', if (Field2= 'lost', if(Field3= between '2021-02 and 2021-04), Field4))

[micheledenardi · Accepted solution]

First of all i've formatted yout Field3 as date:

LOAD
    Field1,
    Field2,
    makedate(left(Field3,4),right(Field3,2),1) as Field3,
    Field4
FROM [lib://BaseFolder/3.DEV/_Temp/Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

Then use set analysis to achieve your result:

Sum({<Field1={'Red'},Field2={'Lost'},Field3={">=01/02/2021","=<01/04/2021"}>}Field4)

[gino2780]

Thank you Michele. That worked.

One more question: let's say i want to build something like a overall sum by combining (addition or subtraction of two or more selections):

1. Example for addition (i have solved it by just adding both expressions). Is there a shorter way to script it without the addition of both expression formulas?

Sum({<[LVV-RPG.Produktgruppe]={
'DL - Abrechnung'
},Status={'Offen'}>}[LVV-RPG.Umsatz])+

Sum({<[LVV-RPG.Produktgruppe]={
'DL - Liegenschaftsanalyse'
},Status={'Offen'}>}[LVV-RPG.Umsatz])

2. Example for subtraction(i have solved it by just subtracting both expressions). Is there a shorter way to script it without the subtraction of both expression formulas?

Sum({<[LVV-RPG.Produktgruppe]={
'DL - Abrechnung'
},Status={'Gewonnen'},[JJJJ-MM]={"=2021-01","=<2021-06"}>}[LVV-RPG.Umsatz])-

Sum({<[LVV-RPG.Produktgruppe]={
'DL - Liegenschaftsanalyse'
},Status={'Gewonnen'},[JJJJ-MM]={"=2021-01","=<2021-06"}>}[LVV-RPG.Umsatz])