Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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.