Problem with variable in set expression

[Anonymous]

Hi,

I have a problem with a SET analysis variable, it won't evaluate through more than the last year.

I have data like:

Year	Month	Data
2013	Jan	25000
2013	Feb	37000
...	...	..
2013	Dec	42000
...	...	...
2018	Sep	102500

.. and so on through all months up until today.

• The first thing I want to evaluate is, if there is 12 months in the year, sum Data for that year in december. So, the sum of 2013 should be 42000.
• Second, if there's no twelfth month, sum Data for the last month in that year, in this case it should sum to 102500 for 2018.
• Third, if I make a selection in the month dimension, let's say I pick April. The sum of Data should be for april for all years.

I have a field in my script (MonthVariable) that puts this data on every year-month: jan-13, feb-13, mar-13 and so on..

This script will output my desired month/year data:

Date(Max(MonthVariable),'MMM-YY') - Let's call it Monthlookup

BUT:

When I use this script to sum the results: Sum({$<MonthVariable={"$(=Date(Max(MonthVariable),'MMM-YY'))"}>} Data)

I get this output:

Year	Monthlookup	Sum of data
2013	dec-13	0
2014	dec-14	0
2015	dec-15	0
2016	dec-16	0
2017	dec-17	0
2018	sep-18	102500

It works as desired when I choose months, but why doesn't it evaluate all the years?

[Anonymous · Accepted solution]

After playing around a little with your script I realized i didn't have to be that advanced.

I have another field "MonthID" i.e: (201809), in the same table as Data, which always contains the last month of data.

I made a script like this:

Max(Aggr(Sum(CustCounter),MonthID))

So much fuss when the answer was quite simple. Hadn't used the Aggr-function before though.

Thanks a lot Rob, Matteo and Neelam for looking into this