Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
PrashantRupani
Creator
Creator

Remove a certain order status from the data set

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 DateOrder CityOrder StatusSalesRevenueProfitLossVariance %Profit %Loss %
1/2/2021NYC Shipped10020020101%5%0%
1/3/2021LondonShipped20030015102%0%10%
1/3/2021MumbaiDelivered3004001053%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 DateOrder CityOrder StatusSalesRevenueProfitLossVariance %Profit %Loss %
1/2/2021NYCShipped10020020101%5%0%
1/3/2021LondonShipped20030015102%0%10%
1/3/2021Mumbai-3004001053%1%0%

 

 

1 Solution

Accepted Solutions
JuanGerardo
Partner
Partner

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

View solution in original post

4 Replies
JuanGerardo
Partner
Partner

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

PrashantRupani
Creator
Creator
Author

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?

 

JuanGerardo
Partner
Partner

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

View solution in original post

PrashantRupani
Creator
Creator
Author

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