Floating dimension and Pick issue

[dana]

Hi People,

I use a floating dim to calculate a total :

LOAD Dual(_Measure_Name,Measure_Code) As _Measure_Name INLINE [
Measure_Code, _Measure_Name
1, Type A
2, Type B
3, Total
];

I have a strange issue: only the first  of the Pick expressions works.

I duplicated the first expression to check whether the issue was with one of the other expressions,

but strangely, it doesn't work in the second position,

I added a constant, and it works fine.

The Pick expression  (the first and second  expressions are identical):

Pick([_Measure_Name],
Sum({<DaysInGarage_Accident ={'Type A'},MonthlyDaysInGarage_Accum ={">3"} >} aggr(Max({<DaysInGarage_Accident ={'Type A'},MonthlyDaysInGarage_Accum ={">3"} >} MonthlyDaysInGarage_Accum),ORDER_NO))
,
Sum({<DaysInGarage_Accident ={'Type A'},MonthlyDaysInGarage_Accum ={">3"} >} aggr(Max({<DaysInGarage_Accident ={'Type A'},MonthlyDaysInGarage_Accum ={">3"} >} MonthlyDaysInGarage_Accum),ORDER_NO))
,1000
)

 The result:

Would appreciate some help!

Thanks!

[Oleg_Troyansky]

Hi Dana,

This has to do with the AGGR() function and how it interacts with synthetic dimensions like the one you are using.

I'm covering this example, among many other advanced analytics scenarios, in my session on Set Analysis and AGGR at the Masters Summit for Qlik.  I will be delivering this session on-line on March 1st.

Check out our agenda and register here:

Masters Summit for Qlik 

Cheers,

[dana]

Hi Oleg,

Thanks for your prompt reply .

Follow your comment,  I Found the resolution here:

https://support.qlik.com/articles/000031589

I will definitely check the workshop you offer!

[dana · Accepted solution]

Following @Oleg_Troyansky  comment, I found this support article, which resolved my issue:

https://support.qlik.com/articles/000031589

[Oleg_Troyansky]

Hi Dana,

Yes, NODISTINCT is one of the possible solutions. Some calculations work well with NODISTINCT and some don't. I think that your calculations of the Max() value should be fine with NODISTINCT.

In my session, I'm covering two other ways of addressing the same problem, for those cases when NODISTINCT doesn't produce the correct result.

See you at the virtual Masters Summit for Qlik!