Broken Expression

[ba11]

Hi Everyone,

I have an expression in QlikSense that keeps returning zero value. For context we recently migrated from QlikView and same expression works fine. I'm also new to QlikSense so I don't really know where the problem could be.

This is the expression:

=num(

SUM({$}

if(PART.PRODUCT_CODE='GTM' or PART.PRODUCT_CODE='VTM' or PART.PRODUCT_CODE='SC',

//SEQ>

if(OPERATION.SEQUENCE_NO=

aggr(

MIN({$}

if(WORK_ORDER.STATUS='R',

if(WORK_ORDER.SUB_ID=0,

if(OPERATION.COMPLETED_QTY<>0,

OPERATION.SEQUENCE_NO

)

)

)

)

,

OPERATION.WORKORDER_BASE_ID

)

,

if(

aggr(

MIN({$}

if(WORK_ORDER.STATUS='R',

if(WORK_ORDER.SUB_ID=0,

if(OPERATION.COMPLETED_QTY<>0,

OPERATION.SEQUENCE_NO

)

)

)

)

,

OPERATION.WORKORDER_BASE_ID

)=OPERATION.SEQUENCE_NO,

if(OPERATION.COMPLETED_QTY<>0,

if((OPERATION.COMPLETED_QTY

-if(IsNull(AGGR(SUM(OPERATION.DEVIATED_QTY),OPERATION.WORKORDER_BASE_ID)),0,AGGR(SUM(OPERATION.DEVIATED_QTY),OPERATION.WORKORDER_BASE_ID))+(OPERATION.DEVIATED_QTY))>=WORK_ORDER.RECEIVED_QTY,

OPERATION.COMPLETED_QTY-WORK_ORDER.RECEIVED_QTY

-if(IsNull(AGGR(SUM(OPERATION.DEVIATED_QTY),OPERATION.WORKORDER_BASE_ID)),0,AGGR(SUM(OPERATION.DEVIATED_QTY),OPERATION.WORKORDER_BASE_ID))

+(OPERATION.DEVIATED_QTY),0

)*if(IsNull(PART_SITE.UNIT_PRICE)=0,PART_SITE.UNIT_PRICE,PART_SITE.UNIT_MATERIAL_COST)

)

)

)

)

),'£#,##0')

I suspect it might have to do with the aggregation. I'll appreciate any insight on how to tackle this.

[marcus_sommer]

The most simple way to validate respectively trouble-shoot a complex expression is to separate each single part as an own expression and then combining them again - against all relevant selection states. No other approach could ensure that really everything works like expected and is returning the wanted results.

Of course this could become quite time consuming and is a rather ugly job. Much better would be to avoid such complex approaches and using more simple expressions - ideally avoiding all if-loops and aggr() constructs by transferring the essential logic into the data-model, for example by creating relevant flag-fields.

Beside this it's not very likely that the expression itself is broken between View and Sense else the underlying data and/or the data-model isn't the same anymore. The slightest change in the load-processing until this application and/or within the settings may impact the results. Therefore I suggest to check the data first. Useful would be to put all relevant fields in a table-box and then applying the relevant selection states to see if there any data available and does they have the needed associations.

[MattiasThalén · Accepted solution]

Tricky to check without knowing the correct answer and not having the data model in front of me.

But like Marcus said, check your data model first. If this works in QlikView, it should work in Qlik Sense, ASSUMING the model hasn't changed.

Anyway, I took some help from a "friend" (ChatGPT) and refactorized the expression a bit by removing nested if statements and putting what's possible in set analysis. After doing this, I couldn't understand why the Aggr() is there.

=Num(
    Sum(
        {$<
            PART.PRODUCT_CODE = {'GTM', 'VTM', 'SC'}
        ,   WORK_ORDER.STATUS = {'R'}
        ,   WORK_ORDER.SUB_ID = {0}
        ,   OPERATION.COMPLETED_QTY -= {0}
        >} 
        If(
            OPERATION.SEQUENCE_NO = 
                // This Aggr should probably be inside something like min/max, other wise, why not just do min without the aggr?
                Aggr(
                    Min(
                        {$<
                            WORK_ORDER.STATUS = {'R'}
                        ,   WORK_ORDER.SUB_ID = {0}
                        ,   OPERATION.COMPLETED_QTY -= {0}
                        >}
                        OPERATION.SEQUENCE_NO
                    )
                ,   OPERATION.WORKORDER_BASE_ID
                )
            And WORK_ORDER.RECEIVED_QTY <=
                    (
                        OPERATION.COMPLETED_QTY
                        -
                        Alt(
                            // This Aggr should probably be inside something like min/max, other wise, why not just do sum without the aggr?
                            Aggr(
                                Sum(OPERATION.DEVIATED_QTY)
                            ,   OPERATION.WORKORDER_BASE_ID
                            )
                        ,   0
                        )
                        +
                        OPERATION.DEVIATED_QTY
                    )
        ,   OPERATION.COMPLETED_QTY
            -
            WORK_ORDER.RECEIVED_QTY
            -
            Alt(
                // This Aggr should probably be inside something like min/max, other wise, why not just do sum without the aggr?
                Aggr(
                    Sum(OPERATION.DEVIATED_QTY)
                ,   OPERATION.WORKORDER_BASE_ID
                )
            ,   0
            )
            +
            OPERATION.DEVIATED_QTY
        ,   0
        )
        *
        Alt(PART_SITE.UNIT_PRICE, PART_SITE.UNIT_MATERIAL_COST)
    )
,   '£#,##0'
)

[ba11]

Thank you, will have another crack at it and see what happens. Thanks again.