topic Set_analysis question in QlikView

Set_analysis question

datanibbler — Fri, 20 Apr 2018 08:14:57 GMT

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

Re: Set_analysis question

niclaz79 — Fri, 20 Apr 2018 09:11:36 GMT

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)

Re: Set_analysis question

datanibbler — Mon, 23 Apr 2018 07:48:50 GMT

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.