Forum Discussion

jimpatel's avatar
jimpatel
Post Patron
2 years ago
Solved

Pivot table logic

Thanks a lot for looking at my post   Any idea for below logic will be appreciated guys    1. I am looking for some sort of idea/logic for below ,    The logic is   a. I have order number, mate...
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi jimpatel ,

     

    supposing you have a data table like:

    Order Material Step Status
    2 225 1 Finished
    2 225 2 Finished
    2 225 3 Waiting
    2 225 4 Finished
    2 225 5 Waiting
    2 225 6 Waiting
    4 32 1 Waiting
    4 32 2 Finished
    4 32 3 Waiting
    8 225 1 Finished
    8 225 2 Finished
    8 225 3 Finished
    8 225 4 Finished
    8 225 5 Waiting
    8 225 6 Waiting
    9 3 1 Finished
    9 3 2 Waiting
    9 3 3 Finished
    9 3 4 Waiting
    9 3 5 Waiting

     

     

    try to plot a matrix visual with step column, material column and a measure like:

    measure = 
VAR _mat = MAX(data[material])
VAR _laststep =
MAXX(
    FILTER(
        ALL(data), 
        data[status] = "Finished"
            &&data[material]=_mat
    ),
    data[step]
)+1
VAR _result =
COUNTROWS(
    FILTER(
        ALL(data),
        data[step] =_laststep
            &&data[material] = _mat
    )
)
RETURN 
IF(
    MAX(data[Step]) = _laststep, 
    _result, ""
)

     

    it worked like:

     

jimpatel's avatar
jimpatel
Post Patron
2 years ago

Thanks a lot for your reply

 

The logic is, For order number 2 and material number 225  - solution (step 5 will be 2) because all step upto 4 is finished (Even though step 3 showing waiting but we can ignore as we are in step 4) , so next step will step 5 , same concept for order number 8 for same material number. 

 

Please let me know if this is not clear

 

Thanks again

FreemanZ's avatar
FreemanZ
Super User
2 years ago

hi jimpatel ,

 

supposing you have a data table like:

Order Material Step Status
2 225 1 Finished
2 225 2 Finished
2 225 3 Waiting
2 225 4 Finished
2 225 5 Waiting
2 225 6 Waiting
4 32 1 Waiting
4 32 2 Finished
4 32 3 Waiting
8 225 1 Finished
8 225 2 Finished
8 225 3 Finished
8 225 4 Finished
8 225 5 Waiting
8 225 6 Waiting
9 3 1 Finished
9 3 2 Waiting
9 3 3 Finished
9 3 4 Waiting
9 3 5 Waiting

 

 

try to plot a matrix visual with step column, material column and a measure like:

measure = 
VAR _mat = MAX(data[material])
VAR _laststep =
MAXX(
    FILTER(
        ALL(data), 
        data[status] = "Finished"
            &&data[material]=_mat
    ),
    data[step]
)+1
VAR _result =
COUNTROWS(
    FILTER(
        ALL(data),
        data[step] =_laststep
            &&data[material] = _mat
    )
)
RETURN 
IF(
    MAX(data[Step]) = _laststep, 
    _result, ""
)

 

it worked like:

 

  • jimpatel's avatar
    jimpatel
    Post Patron
    2 years ago

    For some reason i am getting blank data 

     

    Thanks a lot