Select an specific date value by dimension in set analysis when using a date range

[wale_martins]

Hi,

In one column I'm using a set analysis expression to get all sum(values) between a date range.

Example: sum({<[Date_Operation] = {">=$(=date(vStartDate))<=$(=date(vEndDate))"}>} Aggregate_Diary_PL_Stock_Accum)

With this expression there's no problem and I can get the corresponding value for each dimension in each date.

In another column I'm using set analysis to get the sum(values) in a specific date "vStartDate-1"

Example: sum(total {<[Date_Operation] = {"$(=date(vStartDate-1))"}>} Aggregate_Diary_PL_Stock_Accum)

The problem that I have with that expression is that I need to use 'total' to get the sum(values) correctly, but then I can't get the value for each dimension.

I've already tried total <dimension> but it doesn't work... it only works when using vEndDate intead of vStartDate-1, that's why I suppose that the problem is the Date_Operation of each row.

So, In the second expression I would have to avoid the Date_Operation field, but I can't manage it.

Any help would be appreciated.

Thanks in advance.

[tresesco]

Could you please explain the expected output w.r.t the attached sample - to be sure? 

[wale_martins]

Hi @tresesco 

Thank you very much for your time. Attached you have the expected data (red).  P&L StockAccum. (2) should be the 31/01/19 P&L StockAccum. (2) for each ticker and not the total in each ticker.

[tresesco · Accepted solution]

Hi have worked for one expression and left some for you. 😋 . The main change in expression is use of dimensionality() and replacing set analysis with IF.

If([Date_Operation] >='$(=date(vStartDate))' and [Date_Operation]<='$(=date(vEndDate))',
	If( Dimensionality()=2
	,Aggr( sum(TOTAL <Ticker> If([Date_Operation] ='$(=date(vStartDate-1))', Aggregate_Diary_PL_Stock_Accum)), Date_Operation,Ticker)
	  ,sum(TOTAL {<[Date_Operation] = {"$(=date(vStartDate-1))"}>} Aggregate_Diary_PL_Stock_Accum)
	)
	,
	0
	)   

[wale_martins]

awesome @tresesco, thank you very much! If one day you know how to do it in set analysis, please let me know! 🙂