topic Re: Week over week table with only weeks with all 7 days data in QlikView

Week over week table with only weeks with all 7 days data

mp802377 — Wed, 24 Oct 2018 17:09:29 GMT

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!

Re: Week over week table with only weeks with all 7 days data

dplr-rn — Wed, 24 Oct 2018 17:38:10 GMT

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

Re: Week over week table with only weeks with all 7 days data

dplr-rn — Fri, 26 Oct 2018 13:03:31 GMT

Any luck?

Re: Week over week table with only weeks with all 7 days data

mp802377 — Fri, 26 Oct 2018 13:08:31 GMT

Sadly, no. But thank you for trying

Re: Week over week table with only weeks with all 7 days data

Vegar — Fri, 26 Oct 2018 18:51:45 GMT

Try something like this:

Sum({<Date={"<$(=weekstart(max(Date),0,6))"}>} Field)

https://help.qlik.com/en-US/sense/September2018/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/weekstar…

Re: Week over week table with only weeks with all 7 days data

mp802377 — Fri, 26 Oct 2018 19:40:08 GMT

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.

Re: Week over week table with only weeks with all 7 days data

Vegar — Fri, 26 Oct 2018 21:59:29 GMT

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)