topic Set Analysis on Date in QlikView

Set Analysis on Date

Fri, 18 Mar 2011 15:01:01 GMT

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 !

Set Analysis on Date

Anonymous — Fri, 18 Mar 2011 15:59:19 GMT

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... 😞

Set Analysis on Date

johnw — Fri, 18 Mar 2011 22:09:39 GMT


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.

Set Analysis on Date

Mon, 21 Mar 2011 09:35:45 GMT

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) 😃