Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Using AsOf table for accumulation

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

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?

Anonymous
Not applicable
Author

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.

sunny_talwar

But you are def. going back more than just 41/2017, right?

Anonymous
Not applicable
Author

Yes, correct as in your first statement that I would like  be linked from 01/01/2016 to Week 41/2018

Anonymous
Not applicable
Author

Hello Sunny, did you manage to figure out what I'm doing wrong?

sunny_talwar

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

Capture.PNG

Anonymous
Not applicable
Author

Thank you so much Sunny, this is exactly what I was looking for.