Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to filter on group

  1. #1
    Join Date
    Jun 2012
    Posts
    29

    Post How to filter on group

    I want to group records that fit into the following groups:

    • Past due
    • Due within 30 days
    • Due in 30 to 60 days
    • Due in 60 to 90 days


    I assume I can do something like =IF ([DATECOVERTED]<NOW(); do something) but it's the 'do something' that I'm stuck on.
    Last edited by fly_boyz; 07-03-2012 at 12:24 PM.

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Your report data must be sorted by the date. Without it wont work.

    Then create a derived value that is distinctive for each grouping. Then create a new formula-expression (Data->Add Expression..) and give it a name. This creates a new field that holds the computed result. Print the value of that calculation to see what it does.

    Then use that new column as group column for your group.


    =IF ([DC] < NOW(); -1 ; [DC] / 90)

    will produce -1 for old entries, 0 for due within 30 days, 1 for 60, 2 for 90 etc.

    Then you can group-by this value. In the header, you can then add a second formula, using the previously calculated formula as input to produce human readable names.

    =IF ([formula] < 0; "Past Due"; IF ([formula] = 0; "Due within 30 days"; "Due in " & ([formula] * 30) & " to " & (([formula] + 1) * 30) & " days"))
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3
    Join Date
    Jun 2012
    Posts
    29

    Default

    Thanks Taqua,

    Appreciate the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.