Forum Discussion

YasminYas's avatar
YasminYas
Helper I
7 years ago
Solved

Date filter

I have 4 columns called Date created , Date approved, Date assigned, Date completed.
So Date created shows details of  Idea Status , 
Date approved shows details of  To do Status, 
Date assigned shows details of  Doing Status,
Date Completed shows details for Done Status
So i have requirement , 

  • When a completed date range is selected, it should show all data within that completed date range, For example if I pick 1st Jan 2019 as start date and 30th March 2019 as end date, it should show me all ideas, under each status within this date range, irrespective of if they are in Idea, ToDo, Doing, Done status. Meaning, do not show only completed ideas, but all ideas in this date range.

Please help me one this 
How can this achieved

 

 

Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    Instead of using the Complete date as your filter create a new date filed that is the most recent date of the 4.

     

    AnchorDate =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),cre,app),ass),com)

    RETURN check
     
    you can also use this to create a Status Filed that can beused inside you visual.


    Status =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),"Idea","To do"),"Doing"),"Done")

    RETURN check

4 Replies

  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago

    Instead of using the Complete date as your filter create a new date filed that is the most recent date of the 4.

     

    AnchorDate =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),cre,app),ass),com)

    RETURN check
     
    you can also use this to create a Status Filed that can beused inside you visual.


    Status =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),"Idea","To do"),"Doing"),"Done")

    RETURN check
  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago
    instead of using the date complete use a diffrent date colunm that shows the most recent date
     
    AnchorDate =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),cre,app),ass),com)

    RETURN check

    and use the same function to return a status to split in your visual 

    Status =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),"Idea","To do"),"Doing"),"Done")

    RETURN check
  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago
    instead of using the date complete use a diffrent date colunm that shows the most recent date
     
    AnchorDate =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),cre,app),ass),com)

    RETURN check

    and use the same function to return a status to split in your visual 

    Status =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),"Idea","To do"),"Doing"),"Done")

    RETURN check
  • AnthonyTilley's avatar
    AnthonyTilley
    Solution Sage
    7 years ago
    instead of using the date complete use a diffrent date colunm that shows the most recent date

    AnchorDate =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),cre,app),ass),com)

    RETURN check

    and use the same function to return a status to split in your visual

    Status =

    Var com = Table1[Complete]
    Var ass = Table1[Assigned]
    var app = Table1[Approved]
    Var cre = Table1[Created]

    var check = if(com = blank(),if(ass = blank(), if(app = BLANK(),"Idea","To do"),"Doing"),"Done")

    RETURN check