Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Help in creating a condition

Dear, good afternoon. I tell you that I am making a panel which measures the delivery behavior of my suppliers, so far I have advanced considerably, unless I run into a problem which I have not been able to solve. I have a calculated column which according to certain parameters gives me different conditions,

Delivery Status =
SWITCH( TRUE(),
(VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Reception Status] = "Full Reception" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On time",
(VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Reception Status] = "Partial Reception" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On time",
(VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Reception Status] = "No reception" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On time",
(VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Scheduled] = "Scheduled"), "Scheduled",
(VW_EvaProData2[status] = "Approved" && LASTDATE(VW_EvaProData2[Fecha_Comp]) < LASTDATE(VW_EvaProRec[Fecha_R])) || VW_EvaProData2[Reception Status] = "Forced Reception", "Retrazada",
(VW_EvaProData2[status] = "Pending") && VW_EvaProData2[Fecha_Comp] >= TODAY(), "In Process",
VW_EvaProData2[status] = "Cancelled", "Cancelled")
The poblematica that is presented to me is that in some OC there are 2 types of states, which by definition I must leave them in a retraced state, if one of the lines of the document is in this state

My idea is that those marked states if one of the items of the document are retraced, I change all the states to retraced



2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Syndicate_Admin , If this is calculated column not measure then you need to revisit these

     LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])

     

    They should use related or relatedtable. or nothing if from same table

     

    function in column will give value from entire table

    In column that last date from complete column without filter

     

    In measure that last day in context

     

     

    refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
    https://www.youtube.com/watch?v=Wu1mWxR23jU
    https://www.youtube.com/watch?v=czNHt7UXIe8

     

     

    The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

     

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    2 years ago

    Thank you very much for the answer, but I made a mistake with the code inserted. This is really the code

    Delivery Status =
    SWITCH( TRUE(),
    (VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Receiving Status] = "Reception Complete" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On Time",
    (VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Receiving Status] = "Partial Reception" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On Time",
    (VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Receiving Status] = "No Reception" && VW_EvaProData2[Scheduled] = "" && LASTDATE(VW_EvaProData2[Fecha_Comp]) >= LASTDATE(VW_EvaProRec[Fecha_R])), "On Time",
    (VW_EvaProData2[status] = "Approved" && VW_EvaProData2[Scheduled] = "Scheduled"), "Scheduled",
    (VW_EvaProData2[status] = "Approved" && LASTDATE(VW_EvaProData2[Fecha_Comp]) < LASTDATE(VW_EvaProRec[Fecha_R])) || VW_EvaProData2[Receiving State] = "Forced Reception", "Retrazada",
    (VW_EvaProData2[status] = "pending") && VW_EvaProData2[Fecha_Comp] >= TODAY(), "In Progress",
    VW_EvaProData2[status] = "Cancelled", "Cancelled")

    What it requires is that, if one of the lines associated with the first column is in a backward state, I change all the states to delayed