Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Dynamic Max Date in Set Analysis

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))

6 Replies
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
Not applicable
Author

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))


tab1.PNG

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
Not applicable
Author

Here is the app.

Anonymous
Not applicable
Author

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.

tab2.PNG

Anonymous
Not applicable
Author

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))