Set analysis query

[stig1984]

I have created the following expression to only return sales from the previous financial year up the maximum date in the selected financial year.  The financial years are dual so have an integer element for calculations

Financial Year	Start Date	Max Date
2015	29/11/2014	27/11/2015
2016	28/11/2015	25/11/2016
2017	26/11/2016	01/12/2016 (today)

For example if the user was to select 2017 financial year then the expression needs to return sales from the 2016 financial year up to 03/12/2015.  If the user was to select 2016 financial year then the data should return up to 27/11/2015

It all works well except for the highlighted modifier which is used to filter out dates after the maximum day / month in the selected year, which returns the full years data (for example all of 2016 data)

I do have flags for current financial year but the below expression is to work in a report with a user selected financial year compared to the previous one.

The highlighted section does work if I use just this as an expression so the formula is correct - just not working as a set modifier.  I'm sure i've just missed something simple but can't see it for looking.

NUM(SUM(

   {$<

        [Date Type] = {"Delivery Date"},
       [Calendar Financial Year] = {$(=Max([Calendar Financial Year])-1)},
       [Calendar Date] =
                      {"<$(SetDateYear( MAX(
                                         {<
                                               [Date Type] = {'Delivery Date'},
                                               [Calendar Financial Year] = {$(=Max([Calendar Financial Year]))}
                                          >}
                         [Calendar Date])+2,
                         year(Max(
                                           {<
                                               [Calendar Financial Year] = {$(=Max([Calendar Financial Year]))}
                                          >}
                         [Calendar Date]))-1))"},
       [Sales Document Type] = {'Sales Invoice'},
       [Product Class Id] = {1,7,10,12}
       >}
  [Sales Amount LCY]),
'$(format.currency.integer)')

[Anil_Babu_Samineni]

You may forget Single colon for every set operator

[sunny_talwar]

Are you hoping for this set modifier to be evaluated for each dimension? or is this one per chart evaluation? Would you be able to share a sample?

[ali_hijazi]

how is the fact table linked to this table that you showed above?

[stig1984]

the table above was just a mockup to show the dates involved.

The fact table has a delivery date which links to a calendar table (the app has more than one date type so the date type links the invoice to the delivery date type.

[ali_hijazi]

then you can use Interval Match so that each date in the fact table will be linked to a From and To dates in the table you mentioned above

this way when you select a financial year, related dates will be selected

if you can send me a sample I can send you a resolution

[stig1984]

we do have interval match to create each date in the calendar table, and each date is linked to a financial year - for example 01/04/2016 is financial year 2016, 01/12/2015 is financial year 2016 and so on.  What I want to do is create a set modifier to limit the date form the previous year to the max date in the selected year.

Effectively this would be a 'last year to date' result but limited by the max date in the selected year as opposed to limited by today() so that if a completed year is selected then all of the previous years data is returned, if the current year is selected then only last year to date is returned.

[ali_hijazi]

then your expression would be something like this:

sum({

     <

     financial_Year={$(=max(financial_Year))}

     >

     +

     <

          financial_Year={$(=max(financial_Year)-1)}

     >

[stig1984]

That's giving me a rolling 12 month result.

I'm after the previous years result - for example if 2017 financial year is selected then I get data from 28/11/2015 to 03/12/2015

[stig1984 · Accepted solution]

Found the answer:

SUM( //Gross cases

  {$<

        [Date Type] = {"Delivery Date"},

        Database -= {Market},

        $(function.clear_selections(dimension.calendar)),

  [Calendar Date] = {"<=$(=AddYears(MAX({<[Date Type] = {'Delivery Date'}>}[Calendar Date])+2,-1))"},

  [Calendar Financial Year] = {$(=Max([Calendar Financial Year])-1)},

  [Sales Document Type] = {"Sales Invoice"},

  [Product Class Id] = {1,7,10,12}

  >}

  [Sales Quantity])

Many thanks for everyone's help