I am not sure this is possible, but I would like to calculate a date at the row level and use it as part of the selection for the data in a calculation in the same chart.
For example; We have machine data at a transaction level. Each time a machine is running for a product it logs the up time. Not all the products run all the time, so you will have some months that a product will not have been run. We would like to get a listing of products, a starting date (min of the last 3 months of production) and the total run time in the last three months that the product ran.
Product A | Jun-2014 | 5 hours
Product B | Aug-2012 | 5 hours
Product C | July-2014 | 5 hours
It is easy enough to do something in the load, but I would like to do something in the front end if possible, because the dimension (product) could change.
I can get the starting month with this calculation in a chart: MonthStart(Alt(Max(MonthYear,3),Max(MonthYear,2),Max(MonthYear,1)))
The problem is that I can't figure out how to make that a usable date in the calculation of another field. Set Analysis is only determined once per chart so that is out. Doing a SUM( IF... doesn't work either.
Thanks for the reply, but that's not exactly what I am looking for. Here is a better example of just one product.
Line
Product
MonthYear
Hours Run
1
A
Jan-14
3
2
A
Jan-14
1
1
A
Feb-14
7
2
A
Apr-14
3
1
A
Apr-14
8
1
A
Jun-14
2
So in my results I would want to get Feb-2014 as my starting because that would make 3 months of data for that product (Feb, Apr, Jun). The total that I would want to show is 20, which is the sum of the data for those 3 months.
I can do that in the script, but I would love to be able to do it dynamically because we actually track it at multiple levels (product, category, machine, etc.). To create the data in the script, I would need to do it for every variation.
