Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table as below:
Table:
YearMonth OrderNo. OrderStatus Amount
201601 1111 New Order 1000
201701 2222 New Order 1000
201801 3333 New Order 1000
201901 4444 New Order 1000
201901 3333 Old Order 1000
I want to show only 'New Order' for the latest YearMonth.
Below is my set analysis:
Sum( {<[YearMonth]={"$(=max([YearMonth]))"}, [Order Status]={'New Order'}>} Amount)
However it does not work as my expectation.
Is there any other ways?
Yes there is a better way of doing this
create a YearMonthSerial field in your model with the following logic
Year*12+num(Month) as YearMonthSerial
Now apply the above set analysis as
Sum( {<[YearMonthSerial]={"$(=max([YearMonthSerial]))"}, [Order Status]={'New Order'}>} Amount)
Hi,
Thanks for the advise.
Could you go more detail on the yearmonthserial?
I dont have year and month column in my table.
then you should create this field as
num(left(YearMonth ,4))*12+num(Right(YearMonth,2)) as yearmonthserial
Thanks for the solution.
It works on the table.
However i have a complex table/data rather than this table.
It doesn't work on my real project data.
Would you be able to elaborate as to what isn't working? I mean what are you getting as the output in your real project and what are you hoping to get?
Hi,
Thanks for helping.
I'm hoping the table will only show those 'New Order' for the latest time (latest year and latest month).
However, it keep showing all 'New Order' for every year.
It works on my own simplified tables.
When it goes thru my real files, it just show all new order.
Not sure it is affected by the way i load my files into Qliksense as i have more than 10 excel files and combined into 1 table in Qliksense.
Currently I'm using below set analysis:
Sum( {<[YearMonth]={"$(=max([YearMonth]))"}, [Order Status]={'New Order'}>} OpenValue)
Please find below the outcome:
the outcome
this is the outcome i want.
The table should show all 'New Order' for the latest time (201901). However the table is showing all 'New Order' from 201601 till 201901.
It's working for me as it should be