Activity log and master calendar- trying to keep people who logged 0 on the list

[stevelord]

Hi Community, I have a qlikview straight table totalling up the steps people logged walking each day. It works great, but we have a manual step that involves an iferror(vlookup(),0) in excel to map the steps people logged back to a main list of people and provide 0s for people who didn't log any steps.  I setup a master calendar to help make this manual step go away, so we could select the date range and if people logged they'd be totalled, otherwise they'd have a 0.  I unchecked the 'suppress 0-values' and they show okay on the straight table initially, but the 0-logged people still disappear when I select the dates.  Below is my script.  Any ideas to adjust it?  (I'm going to try right-join the gadget to the calendar and see if that gets it going right, but out of ideas otherwise and thought I'd post here while trying that.)

Straight table looks like:

Dimensions: UserID, First Name, Last Name

Expression1: =Sum(Steps)

Expression2: =count(UserID) //other idea, maybe I'll do an expression to count if registered and see if that forces another row open for the 0-logs too..

TrackedSteps: //this is the regular gadget load used in the qlikview

LOAD UserId,

     Steps,

     TrackedSteps_Date,

     StandardActivityName as Steps_StandardActivityName

FROM

C:\ProgramData\QlikTech\Documents\Data\TrackedSteps.qvd

(qvd);

Temp:  //everything from here on is to make the calendar using first and last steps date from above gadget.  Thinking I might drop above and reload/left join after the calendar below actually, but any other ideas are welcome!

Load 

               min(NUM(TrackedSteps_Date)) as minDate, 

               max(NUM(TrackedSteps_Date)) as maxDate 

Resident TrackedSteps; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

               $(varMinDate) + Iterno()-1 As Num, 

               Date($(varMinDate) + IterNo() - 1) as TempDate 

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

               Date(TempDate) As StepsDate, 

               week(TempDate) As StepsWeek, 

               Year(TempDate) As StepsYear, 

               Month(TempDate) As StepsMonth, 

               Day(TempDate) As StepsDay, 

               YeartoDate(TempDate)*-1 as StepsCurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as StepsLastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as StepsRC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as StepsMonthYear, 

//               ApplyMap('QuartersMap', month(TempDate), Null()) as StepsQuarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as StepsWeekYear, 

               WeekDay(TempDate) as StepsWeekDay 

Resident TempCalendar Where Num(TempDate)<=Num(Now()+1) //some test data on an invalid user had steps logged to the year 2020 in the database, so I put this in to help ensure the calendar doesn't run amok on the main dashboard

Order By TempDate ASC; 

Drop Table TempCalendar;

[stevelord]

One thing, the TrackedSteps_Date was actually renamed to StepsDate, so I did have the steps table linked to the calendar on StepsDate.  Still things crumble when I begin making selections on the StepsDate listboxes even when the charts are holding people who logged nothing beforehand.  Adding a date range in script seems to be the only way I can confine to certain date ranges and still keep the 0 people in the tables, but end user will not be able to edit date ranges in script.