Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a straight table in QS.
Need to keep 'Shipped' Order status. In other words remove all statuses but keep only Shipped. There can be many order statuses like not shipped, order placed, shipped, delayed, delivered etc..so on
Order Date | Order City | Order Status | Sales | Revenue | Profit | Loss | Variance % | Profit % | Loss % |
1/2/2021 | NYC | Shipped | 100 | 200 | 20 | 10 | 1% | 5% | 0% |
1/3/2021 | London | Shipped | 200 | 300 | 15 | 10 | 2% | 0% | 10% |
1/3/2021 | Mumbai | Delivered | 300 | 400 | 10 | 5 | 3% | 1% | 0% |
I am writing this expn for Order status column : Aggr(Only( {< OrderStatus={'Shipped'}>} OrderStatus), Order Status)
But then I get is below. This is not what I want. I want this entire row to be removed. How can I achieve this?
Order Date | Order City | Order Status | Sales | Revenue | Profit | Loss | Variance % | Profit % | Loss % |
1/2/2021 | NYC | Shipped | 100 | 200 | 20 | 10 | 1% | 5% | 0% |
1/3/2021 | London | Shipped | 200 | 300 | 15 | 10 | 2% | 0% | 10% |
1/3/2021 | Mumbai | - | 300 | 400 | 10 | 5 | 3% | 1% | 0% |
OK, I see now what you want. Try to use the following expression for your "Order Status" dimension:
If([Order Status] = 'Shipped', [Order Status])
And uncheck the "Include null values" option for this column.
PD.- Aggr() uses large resources, so better if you use only when really needed.
JG
Hi @PrashantRupani ,
Do not use Aggr() unless you find no other way. Try using set analysis in your expressions, like this:
Sales = Sum({<[Order Status] = {'Shipped'}>} Sales)
Revenue= Sum({<[Order Status] = {'Shipped'}>} Revenue)
Profit = Sum({<[Order Status] = {'Shipped'}>} Profit)
Loss = Sum({<[Order Status] = {'Shipped'}>} Loss)
Variance% = Column(3) / Column(2)
etc.
JG
Thanks . This solution striked me . But there are too many measures. I can do that but its consuming.
Just trying to find a shorter way of doing it. Is there any other shorter way of doing it ?
Would aggr function not work?
OK, I see now what you want. Try to use the following expression for your "Order Status" dimension:
If([Order Status] = 'Shipped', [Order Status])
And uncheck the "Include null values" option for this column.
PD.- Aggr() uses large resources, so better if you use only when really needed.
JG
Thanks JG. It worked using Aggr ( I had to uncheck the "Include null values" option for this column.) and also the solution you gave. I am using the solution you provided since Aggr would use large resources.
Thanks for the help on this. I appreciate you putting your time to solve my challenge.
PR