Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Set_analysis question

Hi,

this is actually a follow-up on a previous post, but I'll start a new thread so that things don't become too confused:

I have implemented a formula that is supposed to show orders with an IN-date within a certain month - and the CREATE-date within the same month - before, there were a few tickets that had been created in december 2017 with an IN-date of January 2018.

My formula now looks like this:

>> count(DISTINCT{<Year={$(=max(Year))}, OrderSource={'SHOP'},ProfitCenter={'P10 - Spare Parts'}, CreationYear=P(Year), CreationMonth=P(Month)>}DocumentNumber) <<

The issue is, there is a feature - I don't know why, but it's supposed to be there and it's also supposed to be in other charts - that when someone selects several years for whatever reason, then only the highest selected year should be considered.

As yet, the formula doesn't do that - when I select 2018 and January only, the figures are correct - when I select both 2017 and 2017 and the month of January, then they are not.

I guess I have to build some indirect set_analysis into that >> P(Year) << so that this expression considers not all values made possible by both years selected, but only those made possible by the highest year-selection, no?

I tried to do that, but there must be something more to it. Can someone lend me a hand there, please?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
niclaz79
Partner - Creator III
Partner - Creator III

Hi,

You can apply the max to the CreationYear and CreationMonths as well, instead of the P() version.

count(DISTINCT{<Year={$(=max(Year))}, OrderSource={'SHOP'},ProfitCenter={'P10 - Spare Parts'}, CreationYear={$(=max(Year))},CreationMonth={$(=max(Month))}>}DocumentNumber)

View solution in original post

2 Replies
niclaz79
Partner - Creator III
Partner - Creator III

Hi,

You can apply the max to the CreationYear and CreationMonths as well, instead of the P() version.

count(DISTINCT{<Year={$(=max(Year))}, OrderSource={'SHOP'},ProfitCenter={'P10 - Spare Parts'}, CreationYear={$(=max(Year))},CreationMonth={$(=max(Month))}>}DocumentNumber)

datanibbler
Champion
Champion
Author

Hi Niclas,

thanks! I thought of that - in principle - but I tried to combine it with the P() - I will try that. Thanks a lot!

Best regards,

DataNibbler

P.S.: Hmm, it didn't work with the month - when I select just one month, that part of the set_expression causes the formula to fail. However, that is not intended. If anyone were to select several months, there's no guarantee that the figures will be correct, neither way - if someone selects Jan to March, there's no telling if the tickets shown for March were all created in March or maybe earlier ...

I guess it would make things enormously complicated to do that, no? Basically, I think I'd need one set_expression for every month selected, so I wouldn't be able to stick with the chart at all ...

I'll have a look at the data structure.