Date in Blocks

[Anonymous]

Hi

I have a list of absences that I want to report on.  Rather than show each date I want to show the date in blocks.  Please see my data below:

Joe 01/04/14

Joe 02/04/14

Joe 03/04/14

Joe 15/07/14

Joe 16/07/14

Joe 12/08/14

Bill 01/07/14

Bill 02/07/14

Bill 20/11/14

Bill 21/11/14

Bill 15/12/15

I want to output :

Joe 01/04/14 - 03/04/14

Joe 15/07/14 - 16/07/14

Joe 12/08/14 - 12/08/14

Bill 01/07/14 - 02/07/14

Bill 20/11/14 - 21/11/14

Bill 15/12/14 - 15/12/14

Any thoughts?

Thanks

Phil

[danieloberbilli]

Something like that?  Please find attached

[Anonymous]

do you want data in this way .

Please see below qvw.

thanks

BKC

[anbu1984]

T1:
Load Employee,Date#(Date,'DD/MM/YY') As Date Inline [
Employee,Date
Joe,01/04/14
Joe,02/04/14
Joe,03/04/14
Joe,15/07/14
Joe,16/07/14
Joe,12/08/14
Bill,01/07/14
Bill,02/07/14
Bill,20/11/14
Bill,21/11/14
Bill,15/12/15 ];

T2:
Load *,If(Employee<>Peek(Employee),AutoNumber(Employee&Date),If(Date=Peek(Date)+1,Peek(Grp),AutoNumber(Employee&Date))) As Grp  Resident T1 Order By Employee,Date;

Final:
Load Employee,Date(Min(Date),'DD/MM/YY') & ' - ' & Date(Max(Date),'DD/MM/YY') Resident T2 Group By Employee,Grp;

Drop Table T1,T2;

[Anonymous]

I have simplified my table for the example, I need it to be in a chart expression.

Is this possible?

Phil

[anbu1984]

Your example is missing

[anbu1984]

Check this app

[Anonymous]

I mean, my table in my real script is huge with several joins etc, so I do not want to change my whole script just to output a table.

If I can do it as an expression that would be better.

Phil

[ankitaag]

Hey,

Can you please explain your script?

[anbu1984]

I think you have to create a group like below in script. Then you can find min and max in Chart using group

Load*,If(Employee<>Peek(Employee),AutoNumber(Employee&Date),If(Date=Peek(Date)+1,Peek(Grp),AutoNumber(Employee&Date)))As Grp  Resident T1 Order By Employee,Date;