Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to write a formula that counts certain orders.
Fields : Status Flag, Order Number and Case Number
The status flag tells me what is going on with the case numbers for each order. i am trying to count the number of orders that have case numbers with a status flag of "30" but also has a status flag of "00"
Status Flag |
00 |
01 |
10 |
30 |
60 |
95 |
how do i display this in my expressions to show only these orders for this formula?
Use this set analysis in your expression
{<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}
I tried that before asking and it errors saying garbage after expression "="
What exact expression did you use?
{<[Order Number] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}
i copied what you have here i just replaced the field names with the ones i actually have which is this
{<[ORDER_NO] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>}
and it shows this error Garbage after expression: "="
But where is the expression my friend? This is set analysis... but where is Sum or Avg or Min or Max of a field?
i apologize not sure. i am trying to display these orders in a straight table so for the orders expression. i added this expression. i don't want to do any calculations like Sum Avg or Min or Max. i only want to display all other orders that meet this criteria. so all orders that have cartons with a status 00 and 30 within the same order
You want to show this in a straight table? Without an expression? Okay, may be add a dummy expression like this
Dimension
ORDER_NO
Expression
Count({<[ORDER_NO] = p({<[Status Flag] = {'30'}>})*p({<[Status Flag] = {'00'}>})>} ORDER_NO)
and then on the presentation tab, just hide your expression column so that you only see your Dimension. Does that make sense?
yes sir thank you so much!