Stock at end of period expression - challenge

[jcamps]

Hi,

I know this is a common question but I have not seen any answers that help with our specific situacion.

Lets say I have a table like so:

Product	YearMonth	Week	Date	StockValue
A	201501	1	2015-01-01	1
A	201501	1	2015-01-02	2
A	201501	2	2015-01-10	3
A	201501	2	2015-01-11	4
A	201502	5	2015-02-01	5
B	201501	2	2015-01-11	4
B	201502	5	2015-02-01	5

The stock value when multiple days are selected is obviously the last selected day (sum of all products).

• Select week 1: StockValue = 2
• Select YearMonth 201501: Stock Value = 4A+4B = 8
• Select everything: StockValue =  5A + 5B = 10
  

The following does the trick:

     sum({<Date={'$(=date(max(Fecha))'}>} StockValue)

The problem is, if I put that expression in a chart with time dimension (for example, month by month stock history), it will only get a value for the last period, like so:

Week:	1	2	5
Stock expression:			5

For this case, I'm able to get the result with an AGGR expression looping on the main chart dimension (week in this case)

My problem, I need ONE SINGLE expression than can work on both cases. I need something like $(=StockValue), that can be used in multiple charts, with any one or multiple dimensions that can be anything (week, monthyear, product, customer, whatever).

Haven't been able to solve the problem so far.... Any ideas?

[marcus_sommer]

$(=date(max(Fecha)) creates a variable which will be calculated for the entire chart - this meant it will be calculated first and then used for all rows within the chart. Therefore you need another approach, maybe like this:

firstsortedvalue(StockValue, -Date)

- Marcus

[jcamps]

Thanks Marcus Sommer,

I tried that approach as well. There are multiple StockValues that need to be added, so a single firstsortedvalue is not working. Trying to sum the firstsortedvalue's by product needs an aggr - and for it to work in a chart I need to add the chart's dimension in the aggr. Since the dimension can be different I cannot accomplish the "only one expression" need...

Maybe I did not understand your idea, I'm attaching a simple file with the tables, expression and expected values... Maybe that can give some more insight on my problem...

Thank you again for your help,

[marcus_sommer · Accepted solution]

Yes, I think to wrap the firstsortedvalue in an aggr is a good way. The variable dimensions could be handled in a classic way with if-loops or a pick(match()) or per getobjectfield() - see: Re: How to get the dimensions of the current chart?

- Marcus

[jcamps]

Great! You pointed me in the right direction!

This is the expression that works:

     if (Dimensionality()=0 or match(GetObjectField(), 'Week', 'YearMonth')=0,

            // No dimension, total row, or a dimension that is not a time field

                 sum ( aggr ( FirstSortedValue(StockValue, -Date), Product) )

            ,

            // A time dimension

            pick(match(GetObjectField(), 'Week', 'YearMonth'),

                      sum ( aggr ( FirstSortedValue(StockValue, -Date), Week, Product) ),

                      sum ( aggr ( FirstSortedValue(StockValue, -Date), YearMonth, Product) )

            )

     ) 

I would not describe the solution as "a classic way" hehe - but it does work. Also, its clearly suboptimal since all the sum's get evaluated. But I cannot see a way to choose the specific aggr in a dollar sign expansion, since it depends on the object's GetObjectField.

[marcus_sommer]

It's a great solution and an if-loop is a very useful construct. With "classic" was only meant that by many conditions the use of complex nested if-loops could be often repaced with other more easier and performant logics - I use often the pick(match()).

- Marcus

[jcamps]

I got a slightly better solution, especially for cases where there are many time dimensions to consider. As I said before, (as far as I know), my previous solution forced QV to calculate all the cases (each and every aggregation-sum), then choose among the results.

Since the GetObjectField() function can be evaluated once per object (and not per row), my final, more performant solucion, is:

  fStockValueExpression_v2:

          if (Dimensionality()=0,

                // Total row

                sum ( aggr ( FirstSortedValue(StockValue, -Date), Product) )

          ,

                sum ( aggr ( FirstSortedValue(StockValue, -Date), $(=$(fStockValue_AggrDimension))) )

          )

Then, add a second expression that will be evaluated once per chart and will return the specific dimensions as text, to be added to the above expression before it even gets evaluated:

    fStockValue_AggrDimension:

          if (GetObjectField()='Week' or GetObjectField()='YearMonth',

                pick(match(GetObjectField(), 'Week', 'YearMonth'),

                      'Week, Product',

                      'YearMonth, Product')

                ,  'Product' )

I'm attaching another version, with this solution as well, in case anyone else finds it useful.

Thanks Marcus for your help here,

- Jordi