
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Week over week table with only weeks with all 7 days data
Hello,
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.
Thank you,
Martha
My WeekStart is set for Sunday. My date field is Date. I am just summing sever lines. Like sum(field)
Thank you!
- Tags:
- qlikview_scripting

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not sure if this is the best approach but i believe this will work. create a flag like below to identify if the max week is a partial week. then use set analysis to exclude.
Load Max(Date) as MaxDate
resident yourtable
save this into a variable
and use below to mark the row as a partial
if(num(WeekDay(MaxDate))=6,'N',if(Date<=MaxDate and CalDate>=WeekStart(Date),'Y','N')) as PartialFlag,
hope it helps

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Any luck?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sadly, no. But thank you for trying

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try something like this:
Sum({<Date={"<$(=weekstart(max(Date),0,6))"}>} Field)
Qlik Community MVP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That is what I have now (pretty much)
=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 jgl cia @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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
If you need both upper and lower borders try this
Sum({<Date={">=$(=weekend(min(Date),0,6))<$(=weekstart(max(Date),0,6)) "}>} Field)
Qlik Community MVP
