Dynamic Max Date in Set Analysis

[Anonymous]

Hi Is there any way we can get max date for each of the product and use this in set analysis $ expansion?

I've created the expression and it works fine as long as I select a product name but it does't calculate max date separately for each of the product sum sales.

Sum({<FutureDate>}TOTAL <ProductName>aggr(sum(distinct{<ProductName,Date={"$(=Date(Max({<FutureDate>}aggr(Max({<FutureDate>}Date),ProductName,Date)),'DD/MM/YYYY'))"} >}Sales)

,ProductName,Date))

[sunny_talwar]

Not within set analysis, but may be using another layer of aggr()

Sum({<FutureDate>}TOTAL <ProductName>aggr(sum(DISTINCT Aggr(IfDate= Max(TOTAL <ProductName> Date),Sales), ProductName, Date)), ProductName,Date))

or something on the similar lines

[Anonymous]

Hi Sunny

I've managed to calculate this fine and getting the right values. Now the problem is I also have some future dates called Date2. And if I select any of the future date I get 0 calculated.

here is my expression

Sum(TOTAL <ProductName>

Aggr(If(Count({<FutureDate>}Sale) = 0,

Sum({<FutureDate>}TOTAL <ProductName>aggr(sum({<FutureDate>} distinct

aggr(if(Date=Date(Max(Total <ProductName>{<FutureDate>}Date),'DD/MM/YYYY')

  ,Sale),ProductName,Sale,Date)

  ),ProductName,Date))

)

, ProductName,Date))

is there any way i can see the same data in the straight table but also able to select future date and see only future date product?

[Anonymous]

Here is the app.

[Anonymous]

I think the problem is here. When I select a FutureDate, then the Date becomes unavailable to compare it with the max date. I think in order to make it work I'll need to ignore the Future date selection.

[Anonymous]

Hi Sunny,

Any idea how can we solve this?

Thanks

[sunny_talwar]

What is your desired output? Can you try this?

Sum(TOTAL <ProductName>

Aggr(If(Count({<FutureDate>}Sale) = 0,

Sum({<FutureDate>}TOTAL <ProductName>aggr(sum({<FutureDate>} distinct

aggr(if(Only({<FutureDate>}Date)=Max(Total <ProductName>{<FutureDate>}Date)

  ,Only({<FutureDate>}Sale)),ProductName,Sale,Date)

  ),ProductName,Date))

)

, ProductName,Date))