Filter Out Current Week in WoW Card

mburstein
mburstein Member
in Charting

Hi, I'm creating a Period Over Period card that displays number of tickets created as a week over week comparison.  I'm running into an issue:

 

I don't want to include the current week since the results are incomplete. 

  • When I use Date Range = This Year, Graph by = Week, and Compare to = 1 Week ago:  The Card includes all 52 weeks of the year, even though 14-52 are blank.

Screen Shot 2020-03-24 at 12.58.03 PM.png

  • When I use Date Range = YTD instead - it will include the current week.  I tried filtering out the current week using a beast mode comparing YEARWEEK('date') and YEARWEEK('SYSDATE()), which get's rid of the current week in my YTD but not the comparison (1 week ago).

Screen Shot 2020-03-24 at 12.58.44 PM.png

 

Is there a better way to exlucde the data from the incomplete current period from these PoP cards?

 

Tagged:

Comments

  • Cartergan
    Cartergan Contributor

    Would you be able to add Number of Tickets as a filter and filter out all Null/empty values?

  • mburstein
    mburstein Member

    I'm not sure how I would do that?

     

    I tried the following with no luck:

    • Using ticket id = null as a filter, but that doesn't work since there are no null/empty values
    • I'm unable to use an aggregate as a filter (i.e. COUNT("id"))
    • I also tried a beast mode IFNULL("id",0) and filtering out 0
  • Cartergan
    Cartergan Contributor

    My apologies, you're correct in saying that approach doesn't work. It seems like your data starts with a date table and then joins in the ticket responses. Would it be possible to join the date to your responses so there are no blank dates?

  • mburstein
    mburstein Member

    I'm not actually joining any data here.  I'm just pulling in tickets with x= "Created Date" and Y= Count of "id".  Both values are always present.  Even so, my date filter is already ignoring empty dates by only pulling in data for a specified range.

     

    The standard table is fine, it's just the "1 Week Ago" data that Domo is dynamically pulling that is suspect.

  • Cartergan
    Cartergan Contributor

    Try this:

    CASE WHEN CONCAT(YEAR(`Date`),WEEK(`Date`)) = CONCAT(YEAR(CURRENT_DATE()),WEEK(CURRENT_DATE())) THEN 1 ELSE 0 END

    Then use this as your filter. Hopefully I'm understanding right haha!

  • Cartergan
    Cartergan Contributor

    Scratch that: the week over week still shows week 13. Let me keep playing around with this and I will let you know what I figure out. 

This discussion has been closed.

Categories