14 Replies Latest reply: Jul 21, 2016 12:46 AM by Ramkumar Ramagopalan

# Sum of value for min date

hi,

on a table I have Date Column and then a value column and also a column with flag

on a table chart, I want to have Date, Min Date, Sum values, Value on Min Date.

I used

Sum(if(Flag = 1 and [Date]= Min(Total <[Date]> [Date]),[Value])) to show value of the minimum date but getting null.

Anyone can help.

Thanks

• ###### Re: Sum of value for min date

Try

=Sum( {<Flag = {1}, [Date] = {'\$(=Date(Min({<Flag = 1>}[Date])))'}>} Value)

• ###### Re: Sum of value for min date

Thanks Stefan. it still shows null values

• ###### Re: Sum of value for min date

Are your dates correctly interpreted as such?

Could you upload a small sample?

Why don’t my dates work?

Dates in Set Analysis

• ###### Re: Sum of value for min date

When using the following formula in a column

if(Flag = 1 , Min(Total <[Date]> [Date]))

I get the right date and the format in MM/DD/YYYY

Thanks again

• ###### Re: Sum of value for min date

What is the chart context (dimensions used)?

• ###### Re: Sum of value for min date

The Date Column and it looks like this

date(if(Flag = 1 , [Date]),'MMM YY')

• ###### Re: Sum of value for min date

Still struggling with it.. Anyone can help.

• ###### Re: Sum of value for min date

assuming you are using this in a chart and the chart is ordered by Date, can you try this.

Sum(if(Flag = 1 and [Date]= FieldValue('Date',1), Value)

• ###### Re: Sum of value for min date

it gives null value

• ###### Re: Sum of value for min date

can you post the screenshot with some data and also the expression you have used?

• ###### Re: Sum of value for min date

hi,

here is a screenshot of my data. the date column I am using is the start date and the column I am working to get done is First N S and the expected value needs to be 0.20 which is the value related to the minimum date.

Please note that the dimension is End Date and the start date is something I came up with using the following expression

if(Flag = 1 , Min(Total <[Date]> [Date]))

and End Date is a dimension with the following in it

date(if(Flag = 1 , [Date]),'MMM YY')

• ###### Re: Sum of value for min date

Steps

dimension:

Date

Expression1:

FieldValue('Date',1)

Expression2:

nets

Expression3:

FieldValue('nets',1)

*Sort the chart by the Date dimension and use the Numeric Value sorting.

• ###### Re: Sum of value for min date

and to add to it, the following expression returns the value I am expecting

Sum({<Flag = {'1'},[Name]= {'AB'},

[Date]= {'7/20/2016'}

>}

Total <[Date]> [Value])

• ###### Re: Sum of value for min date

I finally got it to work using the following in set analysis

[Date]= {'\$(=Min({<Flag= {'1'}, Name= {'AB'}>}Total <[Date]> [Date]))'}