Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik gurus, I have been using Qlikview for less than 1 year. I hope this doesn't seem like a silly question, but I've been going around in circles with what I am trying to achieve.
My data is related to my company's mobile application. I am trying to achieve cumulative view of various subscriber counts over weekly and monthly dimensions.
I have implemented the AsOf table in my model but am struggling to get a count of all subscribers who have a "First Login Date" prior to the week/month dimension.
I am able to get count of subscribers who have a "First Login Date" during a week/month period.
In the attached example,
SUBS_TOTAL is the distinct count of all subscribers who have a "First Login Date" prior to the end of Week 2018/41
SUBS_NEW is the distinct count of all subscribers who have a "First Login Date" during this Week 2018/41
Please could you help me with this. Much appreciated.
I have also attache a sample of my current Qlik model.
Message was edited by: Brent Kelly I realized I uploaded a sample version of my app with sensitive data and replaced it with a toned down version.
Check the attached.... I have added this script on the DWH LOAD
MinDate:
LOAD Min(Date) as MinDate
Resident Calendar_Master;
LET vMinDate = Peek('MinDate');
Concatenate (AsOfPeriodTable)
LOAD DISTINCT [Date] as AsOfPeriod,
Year([Date]) as AsOfYear,
Month([Date]) as AsOfMonth,
Week([Date]) as AsOfWeek,
'All' as PeriodType,
Date([Date] + 1 - IterNo()) as [Date AsOf]
Resident Calendar_Master
While [Date] + 1 - IterNo() >= $(vMinDate);
DROP Table MinDate;
To get this
When you say total, are you only going back 1 year for SUBS_TOTAL or starting of time in your app? The reason I ask this is because right now your AsOfTable is only going back 1 year... meaning
AsOfWeekYear 41/2018 is linked to WeekYear 41/2017 till 41/2018... is that what you want or do you want it to be linked from 01/2016 to 41/2018?
Hello Sunny,
When i say total, I mean from the earliest date for which I have data up until AsOfWeekYear 41/2018 (but cumulatively per AsOfWeekYear.
My data only starts around April/May 2017, however my calendar from Jan 2016.
Hope that makes sense.
But you are def. going back more than just 41/2017, right?
Yes, correct as in your first statement that I would like be linked from 01/01/2016 to Week 41/2018
Hello Sunny, did you manage to figure out what I'm doing wrong?
Check the attached.... I have added this script on the DWH LOAD
MinDate:
LOAD Min(Date) as MinDate
Resident Calendar_Master;
LET vMinDate = Peek('MinDate');
Concatenate (AsOfPeriodTable)
LOAD DISTINCT [Date] as AsOfPeriod,
Year([Date]) as AsOfYear,
Month([Date]) as AsOfMonth,
Week([Date]) as AsOfWeek,
'All' as PeriodType,
Date([Date] + 1 - IterNo()) as [Date AsOf]
Resident Calendar_Master
While [Date] + 1 - IterNo() >= $(vMinDate);
DROP Table MinDate;
To get this
Thank you so much Sunny, this is exactly what I was looking for.