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