New to Qlik Sense

[tan_chungkam]

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?

[vishus913]

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)

[tan_chungkam]

Hi,

Thanks for the advise.

Could you go more detail on the yearmonthserial?

I dont have year and month column in my table.

[vishus913]

then you should create this field as 

num(left(YearMonth ,4))*12+num(Right(YearMonth,2))        as yearmonthserial

[tan_chungkam]

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.

[sunny_talwar]

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?

[tan_chungkam]

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.

[sunny_talwar]

It should not be impacted by the way you reload. Would you be able to take a snapshot of what you are getting and through that image let us know what would you not want to see?

[tan_chungkam]

Currently I'm using below set analysis:

 Sum( {<[YearMonth]={"$(=max([YearMonth]))"}, [Order Status]={'New Order'}>} OpenValue)

Please find below the outcome:

the outcomethis 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.

[Shriya]

It's working for me as it should be