Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Need help - Report Filtering

Hi

 

I need help set up a somehwat complex filter for my report.

 

I have a large data set that has a column titled, 'Decscription' which, contains a number of stages within my companies completion process. For example, 'Received Accounts', 'Prepared Draft Computation' etc.

Next to this column, is another with a completion date.

 

I need to create a report that can filter my data by those with a 'Recieved Accounts' Date & a blank 'Completion Date'.

 

I have been able to get my report to filter by those two items within the Description column but how do I get it to also filter by the dates/blank criteria?

 

Any help is greatly appreciated. 

Need Help

7 Replies

  • TeigeGao's avatar
    TeigeGao
    Solution Sage
    7 years ago

    Hi Anonymous ,

    Could you please share some sample data? I can't figure out your table schema, do you want to filter out the item whose 'Recieved Accounts' without a completion date or the completion is another process, this process doesn't have a completion date.

    If it is the previous one, we can use the IF() function to judge it, if it is the later one, we can use the lookupvalue to get the result of the another process.

    Best Regards,

    Teige

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

      Thank you for your response. Find below a picture of the columns I am working with. 

      Each Company has a number of tasks that represent stages in our procedures. The columns reperesent TaskID, Description and also a field containing a date if that stage has been completed.

       

      I would like to generate a table of companies that is filtered by only those that have a date within the Task ID "30" i.e. Recieve final accounts and that has no date within the Task ID "11" i.e. Send to client for signature.

       

      Hope that helps. 

      • TeigeGao's avatar
        TeigeGao
        Solution Sage
        7 years ago

        Hi Anonymous ,

        I think your environment like below:

        We can use the following measure to filter it:

        Measure =
IF (
    MIN ( Table1[TaskId] ) = 30,
    IF (
        CALCULATE (
            MIN ( Table1[End] ),
            FILTER (
                ALL ( Table1 ),
                Table1[ClientCode] = MIN ( Table1[ClientCode] )
                    && Table1[TaskId] = 11
            )
        )
            = BLANK (),
        1
    )
)

        Best Regards,

        Teige