topic Re: Dynamic Max Date in Set Analysis in QlikView

Dynamic Max Date in Set Analysis

Anonymous — Thu, 06 Apr 2017 15:50:00 GMT

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

Re: Dynamic Max Date in Set Analysis

sunny_talwar — Thu, 06 Apr 2017 15:53:30 GMT

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

Re: Dynamic Max Date in Set Analysis

Anonymous — Thu, 06 Apr 2017 22:22:59 GMT

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?

Re: Dynamic Max Date in Set Analysis

Anonymous — Thu, 06 Apr 2017 22:33:46 GMT

Here is the app.

Re: Dynamic Max Date in Set Analysis

Anonymous — Fri, 07 Apr 2017 09:03:21 GMT

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.

Re: Dynamic Max Date in Set Analysis

Anonymous — Fri, 07 Apr 2017 09:34:52 GMT

Hi Sunny,

Any idea how can we solve this?

Thanks

Re: Dynamic Max Date in Set Analysis

sunny_talwar — Fri, 07 Apr 2017 09:38:42 GMT

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