Calculate expression for minimum dimension. (pivot table)

[asgardd2]

Hello!

I have some misunderstanding about how to create the correct expression.

My data model like this:

ID	Date_ForeCast	Forecast
Product 1	01.09.2016	4
Product 1	02.09.2016	4
Product 1	03.09.2016	4
Product 1	04.09.2016	5
Product 1	05.09.2016	5
Product 1	06.09.2016	5
Product 1	07.09.2016	6
Product 1	08.09.2016	6

...                             ...                               ...

I have pivot table in my dashborad:

I want add new expression in the chart, which calculate Forecast for first date and for each product.

I created a formula, and it calculates the correct, but I think it can be done easier. Could you help me with this?

Aggr(Nodistinct

    AGGR(IF(Date_ForeCast = Min(TOTAL <ID> Date_ForeCast),sum(Forecast)),Date_ForeCast,ID)

    ,ID)

I tried make exprressions ! without If statement, but they are not calculated correctly :

expression2:

AGGR(Nodistinct sum({<Date_ForeCast = {"$(=Min(TOTAL <ID> Date_ForeCast))"}>} Forecast),ID)

expression3:

AGGR(Nodistinct sum({<Date_ForeCast = {"$(=Aggr(NODISTINCT Min(Date_ForeCast),ID))"}>} Forecast),ID)

Application in attachment.

Thank You!

Best regards,

Aleksandrov Anton.

[sunny_talwar · Accepted solution]

This is another option and probably better than using double aggregate:

Sum(TOTAL <ID> AGGR(IF(Date_ForeCast = Min(TOTAL <ID> Date_ForeCast), Sum(Forecast)),Date_ForeCast,ID))

[asgardd2]

Thank you for your reply!

[sunny_talwar]

No problem at all