Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
ba11
Contributor III
Contributor III

Broken Expression

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.
Labels (2)
1 Solution

Accepted Solutions
MattiasThalén
Partner - Contributor II
Partner - Contributor II

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'
)

View solution in original post

3 Replies
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
Partner - Contributor II
Partner - Contributor II

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
Contributor III
Contributor III
Author

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