topic Re: Using AsOf table for accumulation in QlikView

Using AsOf table for accumulation

Anonymous — Thu, 01 Nov 2018 08:32:12 GMT

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.

Re: Using AsOf table for accumulation

sunny_talwar — Thu, 01 Nov 2018 13:00:12 GMT

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?

Re: Using AsOf table for accumulation

Anonymous — Thu, 01 Nov 2018 13:11:46 GMT

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.

Re: Using AsOf table for accumulation

sunny_talwar — Thu, 01 Nov 2018 15:40:25 GMT

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

Re: Using AsOf table for accumulation

Anonymous — Fri, 02 Nov 2018 04:11:20 GMT

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

Re: Using AsOf table for accumulation

Anonymous — Mon, 05 Nov 2018 06:23:02 GMT

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

Re: Using AsOf table for accumulation

sunny_talwar — Mon, 05 Nov 2018 19:26:44 GMT

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

Re: Using AsOf table for accumulation

Anonymous — Tue, 06 Nov 2018 11:57:00 GMT

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