Qlikview aggration with conditions

问题:

I have some data like below -

id, fn, ln, logdate
1, Rob, Hep, 01-04-2010
2, Rob, Hep, 09-04-2010
3, Rob, Hep, 10-10-2012
4, Rob, Hep, 01-12-2009
5, Peter, Cheng, 02-10-2010
6, bob, Pen, 05-10-2009
7, Peter, Cheng, 01-10-2009
8, Kim, Rany, 08-01-2010

I will have to write an expression

1. Group the above data by fn+ln.
2. From the Group get the record of min(logdate)
3. For each min logdate from above, find count if the date is above '01-01-2010'

So in this example -

I should get count as one. (Rob Hep's min log date is 01-12-2009 and Peter Cheng's 01-10-2009 and bob Pen's is alos 05-10-2009 all of them are before 01-01-2009)

Can some one help me in writing the expression for it please?

回答1:

First the load script:

Table:
Load id,
      fn,
      ln,
      logdate,
      fn & ' ' & ln as name,
      num(Date#(logdate, 'DD-MM-YYYY')) as numdate inline [
   id, fn, ln, logdate
   1, Rob, Hep, 01-04-2010
   2, Rob, Hep, 09-04-2010
   3, Rob, Hep, 10-10-2012
   4, Rob, Hep, 01-12-2009
   5, Peter, Cheng, 02-10-2010
   6, bob, Pen, 05-10-2009
   7, Peter, Cheng, 01-10-2009
   8, Kim, Rany, 08-01-2010
];

I added one field for the name and an other one for the numeric value of the date. That makes it easier (even possible?) in the set expression.

Then I added a variable vNumdate =num(date#('01-01-2010','DD-MM-YYYY')) so you can edit it in the dashboard.

In the dashboard I have all the fields, a textfield containig the vNumdate and a diagram (type table).

The dimension of the diagram is the field name

And the formulas are:

Min date
=min(numdate)

Count since 1.1.10
=Count({$<numdate = {">=$(vNumdate)"}>} numdate)

And thats the simple dashboard in qlikview:

(Hope I got it right.)