Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
waqqas2426
Contributor

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

Tags (1)
6 Replies

Re: Dynamic Max Date in Set Analysis

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

waqqas2426
Contributor

Re: Dynamic Max Date in Set Analysis

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?

Highlighted
waqqas2426
Contributor

Re: Dynamic Max Date in Set Analysis

Here is the app.

waqqas2426
Contributor

Re: Dynamic Max Date in Set Analysis

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

waqqas2426
Contributor

Re: Dynamic Max Date in Set Analysis

Hi Sunny,

Any idea how can we solve this?

Thanks

Re: Dynamic Max Date in Set Analysis

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