Filter Out Current Week in WoW Card

Reply
Highlighted
Yellow Belt

Filter Out Current Week in WoW Card

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?

 

Tags (1)
Highlighted
Brown Belt

Re: Filter Out Current Week in WoW Card

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

Highlighted
Yellow Belt

Re: Filter Out Current Week in WoW Card

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
Highlighted
Brown Belt

Re: Filter Out Current Week in WoW Card

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?

Highlighted
Yellow Belt

Re: Filter Out Current Week in WoW Card

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.

Highlighted
Brown Belt

Re: Filter Out Current Week in WoW Card

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!

Highlighted
Brown Belt

Re: Filter Out Current Week in WoW Card

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. 

Announcements
Want to join our weekly online meet up? Please visit this board here and let me know. Click here for more details!