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: 
manojkulkarni
Partner - Specialist II
Partner - Specialist II

Aggregaion in Set analysis

Hi All,

I have a scenario where in i need to choose the minimum date of the dimension value in straight table data irrespective of selection date range. Using this date i need to calculate amount.

AGGR(Min({$<Date=,>} Date),PrdCtg) - This is returning minimum date for the PrdCtg

Sum({$<Date={"$(=date(Min(Date)))"},>} Amount) for Amount column. With this expression it is considering the minimum from the selected date range, which is not expected. I am expecting it to pick the date returned by the Aggr function.

How can we achieve this ?

Any suggestions are much appreciated.

Labels (1)
1 Reply
tresesco
MVP
MVP

This is one of the such cases where set analysis can't help. Even if you put aggr() outside the set analysis expression, it is still going to avoid the dimension context of aggr(). It's like - set analysis evaluates once for an entire object (think of a chart with dimension). Hence, your min(Date) - would always give one minimum value against the entire data set the scope and not per dimension value that you pass in the aggr() or in the chart dimension.

So how to tackle such cases ? - avoid set analysis and use Sum(Aggr(if... kind of structure. Or, create additional dimension in the script (usually combination of two or more fields)...and then you could probably be able to use set analysis. For your case, try like:

Sum(If(Aggr(NODISTINCT Min(Date),PrdCtg)=Date,Sales))