Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Update Status Runtime on filter

  Requirement: I need the correct status for each item, both for a single prodid and for multiple prodids. I want to accumulate the same items along with their quantities and update their status ac...
Need Help
Show and Tell
Tips and Tricks
v-zhangti's avatar
v-zhangti
Community Support
2 years ago

Hi, Anonymous 

 

Can you give me an example of your current state results that do not meet your expected output and what the correct output should be?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    v-zhangti 

    Thank you for your response. Unfortunately, due to our server's restrictions within the organization, I'm unable to explain from server.

    Allow me to illustrate the situation using an example:

    I've created a measure named "StatusColumnMeasure," which displays correct results for both single production selection and multiple selections. I also have calculated column, named 'Statuses' I also have attached the pictures below


    My requirement is to have this status information presented in a column format, not as a measure or calculated table, as I need to use this status information in charts as well.

     

    The issue arises when comparing the status of itemid = P100269 in the 1st. For the single production order, the column displays "OH/Need to Order," while the measure shows "OH/Need to Order," which is the correct status.
    Wherease when comparing the status of itemid = P100269 in the 2nd. For the multiple production order, the column displays "OH/Need to Order," That's wrong result. while the measure shows "OH," which is the correct status.


    Note: You can also view the status query to determine the correct status. I'm creating the different status on different QTYs. Let me write the it:


    If REQ quantity is equal to OH quantity then status is OH, If REQ quantity is equal to NTO quantity then status is NTO, If REQ quantity is equal to Ordered quantity then status is Ordered, If REQ quantity is equal to OH quantity + Ordered quantity then status is OH/Ordered, If REQ quantity is equal to OH quantity + NTO quantity then status is OH/NTO, If REQ quantity is equal to NTO quantity + Ordered quantity then status is Partially Ordered. 

    Let me write Status Measure and Columsn queries. . 
        

    StatusColumnMeasure =
    SWITCH (
        TRUE (),
        [ReqQty] = [NToQty] && [OHQty] = 0 && [OrdQty] = 0, "Need to Order",
        [ReqQty] = [OHQty] && [NToQty] = 0 && [OrdQty] = 0, "OH",
        [ReqQty] = [OrdQty] && [OHQty] = 0 && [NToQty] = 0, "Ordered",
        [ReqQty] = [OHQty] + [OrdQty] && [NToQty] = 0 && [OHQty] <> 0 && [OrdQty] <> 0, "OH/Ordered",
        [ReqQty] = [OHQty] + [NToQty] && [OrdQty] = 0 && [OHQty] <> 0 && [NToQty] <> 0, "OH/Need to Order",
        "Partially Ordered"
    )
     
    Statuses =
    IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[NTOQTYSUM] && PSInventShortAccReportTable[OHQTYSUM] = 0 && PSInventShortAccReportTable[OrdQTYSUM] = 0, "Need to Order",
    if( PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] && PSInventShortAccReportTable[NTOQTYSUM] = 0 && PSInventShortAccReportTable[OrdQTYSUM] = 0, "OH",
    IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OrdQTYSUM] && PSInventShortAccReportTable[OHQTYSUM] = 0 && PSInventShortAccReportTable[NTOQTYSUM] = 0, "Ordered",
    IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] + PSInventShortAccReportTable[OrdQTYSUM] && PSInventShortAccReportTable[NTOQTYSUM] = 0 && PSInventShortAccReportTable[OHQTYSUM] <> 0 && PSInventShortAccReportTable[OrdQTYSUM] <> 0, "OH/Ordered",  
    IF(PSInventShortAccReportTable[ReqQTYSUM] = PSInventShortAccReportTable[OHQTYSUM] + PSInventShortAccReportTable[NTOQTYSUM] && PSInventShortAccReportTable[OrdQTYSUM] = 0 && PSInventShortAccReportTable[OHQTYSUM] <> 0 && PSInventShortAccReportTable[NTOQTYSUM] <> 0, "OH/Need to Order",
    "Partially Ordered"
    ))))
    )


    I have pasted this just understand the status which is depedent on QTYs.