Set Expression for YTD prev year and current year

[#1]

HI

I have a calendar table with a month-Id (MY_ID) built as year(date)*12 + month(date) - 1, so no gaps and a nice sequence.

In addition my kalendar table has fields for the year, monthnr, nice month name short and long ...

My fact data also contains a MY_ID field built with the same above formula.

In my charts in one place I need the previous year YTD and current year YTD and also the values between YTD and end of year for previous and current year (dimensions for the chart are month's and a calculated dimension for the 2 years to show in the chart.

It means I have 2 dimensions - month and year - and 2 expressions - YTD values and YTD to end-year values [for the current year these are forecast values])

Month-selection happens with a separate table called "MonthSelection" which contains part of the calendar table (without the future dates the calendar table needs)

I can get the correct set of MY_ID's and Sales for the current year with:

=sum( {< MY_ID={">=$(=MonthSelection.Year*12)<=$(=MonthSelection.MY_ID)"}>} Sale)

trying to extend that expression to include the previous year somehow does not work but returns all sales values:

=sum( {< MY_ID={"

>=$(=MonthSelection.Year*12)<=$(=MonthSelection.MY_ID),

>=$(=MonthSelection.Year*12-12)<=$(=MonthSelection.MY_ID-12)

"} >} Sale)

How do I get a logical OR between the previous year YTD MY_ID's and the current year YTD MY_ID's? Though the comma would do it.

Juerg

[Nicole-Smith · Accepted solution]

Maybe like this:

=sum( {< MY_ID={">=$(=MonthSelection.Year*12)<=$(=MonthSelection.MY_ID)"}+{">=$(=MonthSelection.Year*12-12)<=$(=MonthSelection.MY_ID-12)"} >} Sale)

[#3]

Hi Nicole

I found an angel - you must be from a different planet!

Thanks a lot for your help

Juerg