Set Analysis with Max Date with Time-based Dimension

[#1]

I'm trying to get the latest non null value of a fields in my QVW.

My expression is:   sum({$<[Month Value] = {"$(=vMaxUnitCostMonth)"}>} [Value])

where vMaxUnitCostMonth is: Max(if([Quantity] > 0 and [Value] > 0, [Month Value], Null()))

This works fine when the dimension is not time based.  When it's time based, it will give me the sum(Value) and seems to ignore the set analysis entirely.

When I modify the sum to: sum({$<[Month Value] = {"$(=num($(vMaxUnitCostMonth)))"}>} [Value])

it returns the correct value, but only for the max dimension. For example, if the dimension is Year, the only value that shows up is the year of the max([Month Value]). It's as though the variable is evaluating first, then checking each dimension for it instead of evaluating in relation to the dimension.

Is there a way to get the variable to evaluate in relation to the dimension? Given:

Year	Month Values	Max([Month Value])
2008	200808, 200809, 200810	200810
2009	200908, 200909, 200911	200911
2010	201007, 201008, 201009	201009
2011	201101, 201102, 201103	201103
2012	null	-
2013	201301, 201302, 201305, 201312	201312

If my Dimension is Year, the only value that populates is 2013 as it's got the highest date of all of them, so I get the unit cost only for that year. When I put just the variable as an expression column, it gives the the correct max([Month Value]) in relation to the dimension. Why does it not do the same when it's inside set analysis?

I've also tried sum(aggr(if( [Month Value] = $(vMaxUnitCostMonth), sum(Value), 0), [Month Value], Year)), but it gives the sum(Value) of the entire year and not the latest value. When I make another chart with Dimensions of Year, [Month Value] and use the aggr(if( [Month Value] = $(vMaxUnitCostMonth), sum(Value), 0), [Month Value], Year), it gives appropriate data, but for each [Month Value] instead of only the max.

Thoughts?

[rubenmarin]

Hi Alex, set analisys is calculated for the whole table, so it will not filter different values for each row, maybe you can try:

sum(if([Month Value] = Max([Month Value]), sum(Value), 0)

If that doesn't works a sample data will be nice for testing.

FYI: In a table, if you remove the colum title, you can see the expression calculated, with $-Values expanded. Check if they are what you are looking.

[#3]

Still no dice. Attaching a QVW.

Tried modifying it to take into account non-null values:  sum(if([Month Value] = Max(if([Inventory Quantity] > 0 and [Inventory Value] > 0, [Month Value])), sum([Inventory Value]), 0)) and that didn't work either.

[rubenmarin · Accepted solution]

Ok, seems it's working with:

Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Year), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Value)

/ Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Year), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Quantity)

[rubenmarin]

I think I did it in the wrong table, for the table with year-month it should be:

Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Year, [Month Value]), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Value)

/ Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Year, [Month Value]), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Quantity)

[#6]

This only works when it's a 1:1 relationship between the dimension and the max(Date). For example, if you were to use a dimension such as product in which the max date for one product was 12-31-13 and the max date for another product was 3-31-11, it'd do the sum(<Date = {"12/31/2013","3/31/2011"} Value), which would not work in the instance that product one has a value for both 12-31-13 and 3-13-11.

For example, given:

Product	Date	Value
Product 1	03-31-2011	39.8
Product 1	12-31-2013	40
Product 2	03-31-2011	12

the resulting table would be:

Product	Value
Product 1	79.8
Product 2	12

instead of

Product	Value
Product 1	40
Product 2	12

because the expression is: sum(<Date = {"12/31/2013","3/31/2011"} Value)

Thoughts?

[rubenmarin]

I don't have the data to debug, but for product it should be:

Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Product), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Value)

/ Sum({<Date={$(=Chr(39) & Concat(Aggr(Date(max({<Value={"*"}, Quantity={"*"}>} Date)), Product), Chr(39) & ',' & Chr(39)) & Chr(39))}>} Quantity)

Set analisys is calculated for the entire table, not for row, so this expression sets the Date with all the dates needed for the table, dimension (Product in this case) will filter to the date needed for each row.