Set an expression result as a filter for end user

qlikster — Fri, 22 Sep 2023 11:17:47 GMT

Hi,

I'm trying to give the end user the possibility to filter on an expression:

if(OrderStatus='Makulerad',0, If ((sum(confirmed_quantity) < sum(ordered_quantity)), (sum(confirmed_quantity) - sum(delivered_quantity)), (sum(ordered_quantity) - sum(delivered_quantity))))

They want to be able to filter on everything over 0, everything under 0 and 0 which works when using smart search >0, <0, 0 and so on.

I've tried putting this in an calculated dimension with aggr and that sort of works but I need to aggr over a lot of dimensions so it's not a good solution.

I've also tried aggregation in the script outside the fact table and then join the results to the fact table on OrderId.

But I get an error:

The following error occurred:

Error in expression: Nested aggregation not allowed:

SumOverUnder:

Load [Supplier Order ID],

sum(if(OrderStatus='Makulerad',0, If ((sum(confirmed_quantity) < sum(ordered_quantity)), (sum(confirmed_quantity) - sum(delivered_quantity)), (sum(ordered_quantity) - sum(delivered_quantity))))) as OverUnderDelSum

Resident Facts group by [Supplier Order ID];

Any ideas?

Re: Set an expression result as a filter for end user

anat — Sat, 23 Sep 2023 12:37:54 GMT

SumOverUnder:
Load [Supplier Order ID],
if(OrderStatus='Makulerad',0,
If ((sum(confirmed_quantity) < sum(ordered_quantity)), (sum(confirmed_quantity) - sum(delivered_quantity)), (sum(ordered_quantity) - sum(delivered_quantity)))) as OverUnderDelSum
Resident Facts group by [Supplier Order ID];

SumOverUnder:
load [Supplier Order ID],
if(OrderStatus='Makulerad',0,
If (cq < oq, cq - dq,oq- dq) as OverUnderDelSum
;
Load [Supplier Order ID],
sum(confirmed_quantity) as cq,
sum(ordered_quantity) as oq,
sum(delivered_quantity) as dq

Resident Facts group by [Supplier Order ID];

topic Set an expression result as a filter for end user in App Development

Set an expression result as a filter for end user

Re: Set an expression result as a filter for end user