Dynamic date at the row level

[sicilianif]

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.

Any help would be appreciated.

[MarcoWedel]

Hi,

one possible solution:

hope this helps

regards

Marco

[sicilianif]

Marco,

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.