Caclulated Dimension using AGGR and if

[shanemichelon]

Hi.  I have a question regarding use of the AGGR function to filter a table.

Here is the scenario that caused me to begin this particular journey down a rabbit hole:

The requirement is to create a table showing stock items that have no recent transactions.  The definition of 'recent' is set by using a variable and a slider.  So basically, I wanted to create a table with a single dimension of stock item but only for those items where there had been no transactions for a period of time.

This is what I came up with as the expression for the calculated dimension:

=if(aggr(Max(transDate),StockItem)<reloadtime()-vAge,StockItem)

I think this gives the correct result, but I'm not sure it is using aggr correctly.

My understanding of aggr is that it gives us a virtual table of one expression and the dimensions mentioned.  In my case it would give something like:

StockItem	Max(transDate)
sc101	1 may 2015
sc102	13 may 2015
sc103	1 apr 2014

The bit I don;t understand is how we can then compare this with an if statement. I think the if statement is comparing the expression of the aggr to give us this:

StockItem	Max(transDate)
sc103	1 apr 2014

Where the two stock items with recent transactions have not met the criteria and have been removed.  Then the result of the if, just gives us the set of stock items remaining (just sc103 in this case).

I think this is all ok and correct, but I cannot find any matching examples where aggr is used in an if for a calculated dimension.

Thanks for any feedback.

[tresesco]

Aggr doesn't take calculated dimension as argument. However, you could possibly try something like:

=aggr(Max( If(transDate <reloadtime()-vAge,transDate)),StockItem)

If you find that I didn't get your point right, please try to share a qvw with sample data explaining the expected output.

[shanemichelon]

Thanks tresesco.  But I think your solution will only give the date as the dimension and not the stock item.

The If statement would return the data to the Max statement.  Then the date would be aggregated over Stockitem.  This would give us one date for each stock item.  The date would be the last transaction date for each stock item.

As I said, I think my solution works, but I guess I am after a  guru to let me know if my take on how it works is correct.

[tresesco]

Well, let me go back to your expression (since, it gives what you want).

The If statement would return the data to the Max statement.  Then the date would be aggregated over Stockitem.  This would give us one date for each stock item.  The date would be the last transaction date for each stock item.

Not really. It is actually otherwise. If gets the input from the aggr. Max caculates on overall data. I.e. Maximum several transDates for each StockItem are calculated and then it is checked if those max transDates StockItem(which is probably wrong) are satiffying the condition or not.

Edit: corrected

[jonathandienst · Accepted solution]

I think you need this:

=Aggr(If(Max(transDate) < reloadtime()-vAge, StockItem), StockItem)

[tresesco]

Yes  !! in his expression  '< reloadtime()-vAge' was actually checking on StockItem (unless it was a typo from him). Nice spotting. In fact my explanation above is wrong in terms of comparison of transDate; it actually wrongly comapres StockItem..

[shanemichelon]

It seems to work with both my original and your solutions:

=if(aggr(Max(d),sc)<ReloadTime()-vAge,sc)

=aggr(if(max(d)<ReloadTime()-vAge,sc),sc)

Both of these give the same (correct)  result!  I think that means that the first case is getting a virtual table of all stock codes with latest dates and then filtering, the second is filtering stock items based on latest date and then aggregating.