Week over week table with only weeks with all 7 days data
Is there a way in my table to only show weeks that have a full set of data? I am pulling in 60 days worth of data. The week is starting on a Sunday. I don't want the current week or the first week if there is less then 7 days of data, it would skew the trend.
My WeekStart is set for Sunday. My date field is Date. I am just summing sever lines. Like sum(field)
=Date(WeekStart(TransDate, 0, DayOfWeekID),'DD-MMM-YY') - The DayOfWeekID lets the users select what day of the week they want to start (I have some that want the week to start on Saturday and some on Sunday, that fixes that).
The problem is I am only grabbing the last 42 days (this is a huge amount of data - I tried 60 days but it killed our servers). The data coming in, for example, is starting 09/13/2018. The pivot table is showing a date of 09-Sep-18 because that is the day of the week for that first dataset. So it is a partial week of data. That is a problem because when they look at this pivot table, they will look at that week (and in most cases the current week as well) and are like WTH when it comes to the data. Why is the data so vastly different those weeks. When I explained to them that they were partial weeks, they said 'well take it out then because when I take this to excel (because pivot tables don't have sparklines) the trends are off'. And that is where I am at right now.
FYI - If the powers that be at jglcia @Qlikview read this, please put this ability in a future release. Many companies out there have a ton of data. We can't bring in a year's worth when we have several million lines of data in just one day. Therefore the trending really needs to count. Oh, and a sparkline in a pivot table would be really cool.
The difference between your and mine expression is that mine contains as SET and a MODIFIER reducing the expression only to consider date values from the previous week (assuming that a week is not complete until you've stepped over to the next week).