Forum Discussion

anileshknpowerb's avatar
anileshknpowerb
Helper III
5 years ago
Solved

Need help on date range slicer

Hello experts,

I have following reporting requirement.

 

Below are my slicers:

Note : Completed Date is NULL for Status='New' and Status='Rejected'


Status                       Completed Date(Date between)
Approved                    02/01/2021 03/02/2021
New                                     NULL

Rejected                               NULL


Table visual:

Name      Status                    Completed Date
Rec1        Approved                 02/10/2021

Rec2        Approved                 02/18/2021   

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   
Rec5        Rejected                    NULL    

Rec6        Rejected                    NULL

Rec7        New                          NULL

Rec8        New                          NULL


The 'Status' slicer is Multiselect enabled. The end user want to see records for all Status and when he choose the date range from 'Completed Date' slicer, he want to see records range between the date selected in 'Completed Date' slicer for 'Approved' status and he want to see records for 'New' and 'Rejected' status as well.

 

For example, If user chooses all Status and chooses Completed Date betwen '02/20/2021' and '03/02/2021' , he should see the below data.

 

Expected output:

Table visual:

Name      Status                    Completed Date

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   
Rec5        Rejected                    NULL    

Rec6        Rejected                    NULL

Rec7        New                          NULL

Rec8        New                          NULL

 

The condiction to show data is:

Where
(Status == 'Approved' AND Completed Date > 02/20/2021 AND Completed Date < 03/02/2021)
OR
(Status == 'Rejected')
OR
(Status == 'New')

 

The issue:

When I choose any date range in 'Completed Date' slicer, it shows data for only records that have dates and doesn't show which dont have dates:

 

Output I ma getting :

Table visual:

Name      Status                    Completed Date

Rec3        Approved                 02/23/2021   

Rec4        Approved                 03/01/2021   

 

How to get expected output?

 

Regards,

Anilesh

  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi anileshknpowerb,

     

    You can not filter null values in Between mode:

    Here is a workaround is to replace the null values with virtual dates, such as any date that qualifies.

     

    Best Regards,

    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support

    Hi anileshknpowerb,

     

    You can drag a column which contains blank value into slicer.

     

    Best Regards,

    Link

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

    • anileshknpowerb's avatar
      anileshknpowerb
      Helper III

      The user requirement is to use dates between option in 'Completed Date' slicer. User want to see what are the Records fall between selected date ranges , along with records that have NULL values for Completed Date.

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support

        Hi anileshknpowerb,

         

        You can not filter null values in Between mode:

        Here is a workaround is to replace the null values with virtual dates, such as any date that qualifies.

         

        Best Regards,

        Link

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.