Filter Out Current Week in WoW Card

mburstein

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.

• 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).

 

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

 

Cartergan

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

mburstein

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

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

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

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

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.