"Transport" the selection on one field onto another - Set_Analysis?

[datanibbler]

Hi,

I have a table in a report that is supposed to show orders with an order_date in a certain month. Easy enough for starters.

The thing is, there is a selection_panel for the month (calculated from the order_date) - but every order also has a CreationDate which does not necessarily fall into the same month ... In this instance, when January 2018 is selected, there are a few orders with a CreationDate in December 2017 - I don't want those - but only in this particular table - there are some other tables and charts in this report and on this sheet which I don't really know, so I'd rather not do anything with global effects, but rather keep whatever I do to this particular chart.

As a selection_statement, that's easy enough - I just have to do "Year(CreationDate)=Year AND Month(CreationDate)=Month" - if just one month is selected; And if several are selected, well, I'll just have to mess around a bit with GetCurrentSelections() or so.

So, if I wanted some global fix, I could set a trigger on that month field to adapt the selection on the CreationDate.

<=> The difficult part is putting that in a set_analysis or something else so that this will affect only this particular chart. I guess I need two more native fields - month(CreationDate) and Year(CreationDate) - and then I can put this in the set_expression - that is, I can do it for the case that only one year and one month are selected ... Can someone lend me a hand with an expression that would be flexible as to the selection of multiple months?

Thanks a lot!

Best regards,

DataNibbler

[jonathandienst]

This sort of analysis is easier if your calendar has the necessary fields. You could create Year and Month fields, but I suggest that you also create a "MonthSequence" field in your calendar, like this

    Calendar:

    CalendarDate,

    ...

    Year(CalendarDate) * 12 + Month(CaldendarDate) as MonthSequence,

    ...

The create a variable vMaxMonthSequence

    Set vMaxMonthSequence = =Max(MonthSequence);  // note 2 = separated by a space

Then your set expression becomes (to get latest OR selected Creation Month/Year

    {<MonthSequence = {'$(vMaxMonthSequence)'}>}      // latest/selected

    {<MonthSequence = {'$(=vMaxMonthSequence - 12)'}, Month, Year>}    // last year relative to latest/selected

For a date range (say last 3 months relative to latest/selected):

    {<MonthSequence = {'>=$(=vMaxMonthSequence - 3)<=$(=vMaxMonthSequence - 3)'}, Month, Year>}     

[datanibbler]

Hi Jonathan,

many thanks!

I do not yet quite understand how that code would work and what it would do, but I'll give it a try and see what becomes of it. I'll be back soon with any feedback.

Best regards,

DataNibbler

[datanibbler]

Hi Jonathan,

I'm sorry, I just don't get it. I think what your code does is not what I need, but I'm not sure. I might have badly explained just what I need to do.

As I understand it, that set_expression you propose, >> {<MonthSequence = {'>=$(=vMaxMonthSequence - 3)<=$(=vMaxMonthSequence)'}, Month, Year>}   << would, upon selecting one month and year, take into account the 3 months prior to that .

That's now what I need to do, however. What I need to do is in a way simpler yet difficult to do if there are multiple months selected:

I certainly have a Year_field and Month_field on the regular calendar_date - however, there is a second date to every order. I have also created a Month_field and Year_field on that one. Now I can expand the set_expression in the formula to simply match the selection on the regular Month and Year. That should work as long as only one month is selected.

I'll be back here shortly if that works.

Best regards,

DataNibbler

[datanibbler]

Hi Jonathan,

I have finally made it - halfway. I have now expanded the set_expression, so that it now reads

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

=> The selection of YEAR and MONTH (both in the calendar) is transported directly onto the fields CreationMonth and CreationYear. That is about as far as my knowledge of indirect set_analysis goes.

=> It seems to work also when selecting multiple months, I have quickly checked.

I see once again that by trying out several variations first, I could have saved both you and myself some time ... sorry for that!

Best regards,

DataNibbler