Unable to Count number of records for a given day in Text Expression (from SQL)

[#1]

Hi all,

I'm trying to create a text object which counts the number of records in a table where the "Trade date" is today (for demo purposes, today is the most recent record in the table, which is using the variable vToday)

I've tried the following expression:

=Count({$<TradeDate= { $(vToday) } >}TradeDate)

This returns 0, which I assume is because vToday holds the date in number format and the Trade dates are in YYYY-MM-DD format. So I created a new variable and formatted it:

LET vTodayDate = date($(vToday), 'YYYY-MM-DD');

But when I replace vToday with vTodayDate in the first formula I get the error "Error in set modifier ad hoc element list: ',' or ')' expected" and I can't seem to shake that error message. Can anybody please suggest why this is the case and a possible solution? Thanks

[#2]

try this

=Count({$<num(TradeDate)= { $(vToday) } >}TradeDate)

and set  vToday= num(datefield)

[#3]

try this

LET vTodayDate = num(date($(vToday), 'YYYY-MM-DD'));

the use it

1. =Count({$<num(TradeDate)= { $(vTodayDate) } >}TradeDate) 

[#4]

Thanks for your reply. I tried this, but the error is now just "Error in set modifier expression" indicating a problem with the second {. Tried seeing why that's the case but no luck so far

[rbecher]

Try:

Count({$<TradeDate= {'=$(=Date(vToday))'} >}TradeDate) 

[#6]

Thanks for your reply Ralf. I tried your solution, but it returned the number 1409, when I only have one record in that table for today's date. I would prefer a solution that doesn't rely on being recalculated and reloaded, although that said, our plan is to reload the data several times during the day in any case due to the nature of the work.

In addition to this, I'd need to be able to calculate the percentage change between yesterday and today based on this solution, which may end up being another discussion post depending on how well I do this afternoon (currently getting nested aggregation error!)

Thanks

[rbecher · Accepted solution]

Works with a range condition only:

=Count({$<TradeDate= {'>=$(=Date(vTodayDate)) <=$(=Date(vTodayDate))'} >} TradeDate)

- Ralf

[rbecher]

You can calculate percentage change between yesterday and today in this way:

=Sum({$<TradeDate= {'>=$(=Date(vTodayDate)) <=$(=Date(vTodayDate))'} >} Value) /

Sum({$<TradeDate= {'>=$(=Date(vTodayDate)-1) <=$(=Date(vTodayDate)-1)'} >} Value)

[#9]

This answer and your answer below regarding the percentage change were exactly what I was looking for and then some - many thanks!