Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Any luck?
Sadly, no. But thank you for trying
Try something like this:
Sum({<Date={"<$(=weekstart(max(Date),0,6))"}>} Field)
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.
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)