# New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator

## Set analysis

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?

Labels (2)

• ### Set Analysis

9 Replies
Partner - Creator

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)

Creator
Author

Hi,

Could you go more detail on the yearmonthserial?

I dont have year and month column in my table.

Partner - Creator

then you should create this field as

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

Creator
Author

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.

MVP

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?

Creator
Author

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.

MVP
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?
Creator
Author

Currently I'm using below set analysis:

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