2 Replies Latest reply on Apr 16, 2019 6:53 AM by Daniela Bistafa

Sum values between dinamic Dates considering a "flag"

Hello!

I'm trying to sum values between two dinamic dates. The "begin date" should be 12 months before the "end date". And the "end date" should be when the "flag" is one (1 means the month closure).

The first problem is that there are zeros and ones for all months (Table in Excel file), so I use a MAX calculation to consolidate them (Result 1 in Excel file). What I need is to sum the values between the maximum date (where de "MAX Flag" is one), and the minimum date (12 months before the max date).

I was able to put the values I need on the month rows, but the "total" sum doesn't work (Final Result), because I used LOD and table calculations.

Any ideas how to do this?

Thank you verry mutch!

• 1. Re: Sum values between dinamic Dates considering a "flag"

First calculate the End Date with an LOD - Get of all the rows where Flag = 1, the max date

{ FIXED : MAX(IF [Flag] = 1 THEN [Date] END) }

The Begin Date is then easy

Now you can identify for each row if it is between the Begin and End Date

[Date] >= [Begin Date] AND [Date] <= [End Date]

• 2. Re: Sum values between dinamic Dates considering a "flag"

Hi, Ewald!

I can't add the field to the filter, because other fields from the same table refer to other dates. But I could sum the 12 months value using your sugestion!

IF [Is between Begin and End date?] THEN [Value] END

Thank you!