How to avoid the N/A when using CASE stmt

Comments

4 comments

  • Avatar
    Hamza Jap-Tjong (Edited )

    Hi Roopa,

     

    The reason why you are getting N/A is because you are selecting/importing all Date values from your fact_energy. 

    In other words you are getting every value and only for the values that are within 30 days are getting a timestamp with your case. 

     

    I would do something like;

     

    SELECT DISTINCT

    DateTableKey-function,

    [site_number],

    [Date]

    FROM

    [FACT_ENERGY]

    WHERE

    [Date] > adddays(now(),-30)

     

    This will only select records of which the date is from 30 days ago till now.

     

    However if you do want to include all records, you can change your CASE-statement with including an ELSE. Something similar to:

    CASE

    WHEN X=Y THEN Z

    ELSE 0

    END

    this will reformat your N/A to a set value

     

     

    0
    Comment actions Permalink
  • Avatar
    Roopa Raghavan

    Hamza,

    Appreciate your quick response to my query. Thank you. I did end up putting a where clause to limit the dates.

    As per your suggestion, if I use an ELSE then the N/A will get set to 0 which again is not what I want (As for a set value for ELSE part, I cant have anything apart from Dates show up, so a 0 or N/A both won't work)

    I tried to find a way to use a radio button filter on my dashboard for dates that had 'Last 30 Days' , 'All' something like that.

    When I select Last 30 days, my visualization should show only last 30 days' dates (no N/A or 0 for date) and when I choose 'All' then all dates in the dataset.

    I haven't found a solution yet for this.

     

    Thanks again for your inputs Hamza.

     

     

    0
    Comment actions Permalink
  • Avatar
    Hamza Jap-Tjong

    Hi Roopa,

    Sisense does offer a dynamic time filter on the front-end, so you do not necessarily have to do a CASE. 

    In your case I would not represent a timestamp in your column. I would take a text value and set it to Yes or No. 

    CASE

    WHEN date > adddays(now(),-30) THEN 'Yes'

    ELSE 'No'

    END

    If your users want to see the exact dates, they can just use the date field.

    0
    Comment actions Permalink
  • Avatar
    Roopa Raghavan

    Hamza, thanks again for your inputs. I'm getting clearer as I go here. Based on what I've read in forums like some other folks I also don't want the out the box Date options in the Date filter that has List,Calendar, Timeframe, Ranking and Advanced features. I only need List and few options from TimeFrame. I guess I was trying to implement this using the CASE statement, but I see your point why it can't also be a date column.

    Thanks again

    0
    Comment actions Permalink

Please sign in to leave a comment.