Skip to main content

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. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
tan_chungkam
Creator
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)
9 Replies
vishus913
Partner - Creator
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)

tan_chungkam
Creator
Creator
Author

Hi,

Thanks for the advise.

Could you go more detail on the yearmonthserial?

I dont have year and month column in my table.

vishus913
Partner - Creator
Partner - Creator

then you should create this field as 

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

tan_chungkam
Creator
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.

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
Creator
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.

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

Currently I'm using below set analysis:

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

Please find below the outcome:

the outcomethe outcomethis is the outcome i want.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.

Shriya
Contributor II
Contributor II

It's working for me as it should be