Set Analysis on Date

[#1]

Hi!

First of all, i'm sorry for the bad english ... I'm a french student.

So, this is my set analysis expression :

=count({<ID_DATE={"$(=date(max(ID_DATE)))"}-{">$(=date(now()))"}>} ID_Bug)

ID_DATE is a date (format : "DD/MM/YYYY") between 01/01/2008 and 31/12/2012.

I have 3 tables :

- Bug(ID_Bug, Status, ...)

- Calendar(ID_DATE, ID_WEEK, ID_MONTH, ID_YEAR)

- BugActive(ID_Bug, ID_DATE, Status)

BugActive contains dates (ID_DATE) for each ID_Bugbetween the date of his creation and the date of his resolution. And his status per day.

With my expression, i would like to get number of ID_Bug by respecting :

- if an ID_DATE is selected, i want to get number of ID_Bug of this day,

- if an ID_WEEK is selected, i want to get number of ID_BUG of the last day of this week,

- if an ID_MONTH is selected, i want to get number of ID_BUG of the last day of this month,

- the same for ID_YEAR

- if an ID_WEEK and an ID_MONTH are selected, i want to get number of ID_Bug of the last day of the week of the month selected.

But, if nothing is selected, i want to get number of ID_Bug of today

I hope you understood me as much as possible.

Thank you for help !

[Anonymous]

No problem with your English 🙂
I didn't test it, so you'll have to try and adjust:
if(GetSelectedCount(ID_DATE) OR GetSelectedCount(ID_WEEK) OR GetSelectedCount(ID_MONTH) OR GetSelectedCount(ID_YEAR)),
count({<ID_DATE={"$(=date(max(ID_DATE)))"}>} ID_Bug),
count({<ID_DATE={"$(=date(today()))"}>} ID_Bug))

And, not matter if this is good or not - never use now() in front end expressions. It will take all CPU it can... 😞

[johnw]

Michael Solomovich wrote: And, not matter if this is good or not - never use now() in front end expressions. It will take all CPU it can... 😞

Technically, now() only polls the operating systems once per second. So if, say, you're just displaying the current time, it will use very little CPU. But if, say, you have a hundred million rows of timestamped data, and you want to ignore the last 24 hours of that data, this:

sum(if(Timestamp<now()-1,Value))

Is going to lock your CPU at 100%, because it will try to recalculate every second, and this expression is very likely to take more than one second to recalculate.

[#4]

Hi !

I just trying this :

if(GetSelectedCount(ID_DATE) OR GetSelectedCount(ID_WEEK) OR GetSelectedCount(ID_MONTH) OR GetSelectedCount(ID_YEAR),
count({<ID_DATE={"$(=date(max(ID_DATE)))"}>} ID_Bug),
count({<ID_DATE={"$(=date(today()))"}>} ID_Bug))

Results are good but if the ID_WEEK '12' (from today to 27/03/2011), max(ID_DATE) = 27/03/2011. I'ts normal but i want to have today's results.

So, i modify this expression by adding :

if(date(max(ID_DATE)) >= date(today()), count({<ID_DATE={"$(=date(today()))"}>} ID_Bug)

Instead of

count({<ID_DATE={"$(=date(max(ID_DATE)))"}>} ID_Bug)

Thanks for help (and for the "today()/now()" difference) 😃