topic Re: Floating dimension and Pick issue in App Development

Floating dimension and Pick issue

dana — Thu, 26 Jan 2023 17:25:32 GMT

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!

Re: Floating dimension and Pick issue

Oleg_Troyansky — Thu, 26 Jan 2023 17:34:11 GMT

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,

Re: Floating dimension and Pick issue

dana — Thu, 26 Jan 2023 17:55:42 GMT

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!

Re: Floating dimension and Pick issue

dana — Thu, 26 Jan 2023 17:56:55 GMT

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

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

Re: Floating dimension and Pick issue

Oleg_Troyansky — Thu, 26 Jan 2023 17:59:29 GMT

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!