Year/month dimensions

[Anonymous]

Hi all

This may get quite convoluted pretty quickly, so bear with me!

Ok, so I'm using Qlik Sense Server.

I have a data connection to quite a large table of data, which is basically client agreements dating back to 2003, and these agreements are for a variety of different products.

The table contains a huge amount of data (over 70,000 agreement details), but the columns I'm specifically talking about in this situation are 'EFF_START_DT', which is the date the agreement started, and 'EFF_END_DT', which, for agreements that have expired/been cancelled etc., the date the agreement ended.

I am building an app which I want to show, by month/year, the number of agreements that ended that month (either showing a full year by month, or select filter on selected months), and the base of agreements still active as of that month.

Now, I have managed to get it working for the Ended subs view, which was relatively simple, as I just made the date dimensions (month and year) based on the EFF_END_DT, (month(EFF_END_DT)), and counting the numbers of subs that ended that year/month, (count(AGREE_NUM)).

However, the issue I have now is I can't for the life of me work out how to incorporate the total number of subs still active as of that same month/year i.e. the EFF_END_DT is null.

Even if I have a completely different App for the base numbers of subs, how do I script something that beings back the number of active subs as of (for example) February 2017, or a full year view of 2016 showing the base growth month by month?

I really don't want to have a separate app though, which adds a whole lot of complexity.

Here is an example of the background script in the data load editor (it's greatly abridged - I'm selecting dozens of columns and have a lot of case statements to group products etc)

Select

AGREE_ID
,AGREE_NUM
,EFF_START_DT
,EFF_END_DT
,AGREE_STATUS

from
CX_LKP_RECURRING_AGREEMENTS_BASE

where EFF_START_DT <= getdate()
and year(EFF_START_DT) >= 2003

Thanks!

[Anonymous]

Thank you Sunny - I genuinely hugely appreciate the help.

I've only just recently started this role at work, and have taken over the development of all the company's Qlik reporting from the guy who built all the existing reporting, who has since left the company.

And I have had zero prior experience in SQL and Qlik, so learning as I go!

I'll give this a go (after adding all the bits to the script I took out for the purposes of this discussion) and come back if there's something catastrophic!

[sunny_talwar]

Sounds like a plan

[Anonymous]

Ok, worked without errors (huzzah!)

However, I have a couple of case/when's included.

Here is one of them:

What's happening is the new 'DIRECT_SUB_CHANNEL' is now no longer being created (the same for the other section where I'm grouping product groups).

[sunny_talwar]

Where was this initially?

[Anonymous]

It was in the "Select" section (I had no "Load" section, of course).

Should I be moving it to the Load section, or having them in both the Load and Select sections?

Edit: Ok, the data load fails if I move it to the Load section and if I have it in both Load and Select.

So, in a nutshell, the data load works ok if it's just in the Select section, but, well, the case section doesn't actually do anything.

Also: here is the current visualisation (with the raw product names - the total numbers should be accurate regardless).

The "Ended Subs" figure is dead on.

However, the "Base" number should be around 30,000 (and growing slightly each month).

Here is the 2016 view - as you can see it's going in completely the wrong direction, at a great rate:

January should be around 23,000, increasing every month to be around 30,000 in December:

My Dimensions:

Month - month(MonthName)

Year - year(Date)

Measures:

Ended subs - Count(DISTINCT Aggr(If(MonthName = EndCheck, AGREE_NUM), MonthName, AGREE_NUM))

Base - Count(DISTINCT Aggr(If(MonthName < EndCheck, AGREE_NUM), MonthName, AGREE_NUM))

It's obviously something to do with the EndCheck, but I can't work it out sorry.

And...is it normal to get "Calculation time out' errors which prevent visualisations opening? I've been getting a lot of them today using this new scripting.

[sunny_talwar]

I am a little lost now, you will have to break down your questions for me. I am not sure what happened with your case statement. Is it not showing up on the front end? Would you be able to share your complete script or ideally your app to see what you trying to do

[Anonymous]

Fair enough - attached is the whole script. I'm not sure I can share the app - I don't have access to the server, so can't get a copy.

In a nutshell, the issues I have are:

• The case statements - sorted. Changed the Load to load PROD_GROUP instead of AGREEMENT_PRODUCT_GROUP etc
• The Base is not calculating correctly - should be around 30,000 each month an increasing slightly each month
• I keep getting 'Calculation Time out' errors when the app tries to load the visualisations - leaving just a red box (similar to when there's an invalid dimension). Could it maybe have something to do with this:

Nearly 20 million lines being fetched is a hell of a lot - my old script only fetched around 60,000 (which is the fist "Lines fetched" figure above).

Or is this not even the case - because of the Calendar is it in fact only fetching 3,657 lines?

Whatever it is, when I commented out the Calendar and Interval sections there was no issue at all with them loading. Loaded with no data, of course, but...

Thanks again for your help Sunny - I realise it's probably turned into a bit of a quagmire, so I wont be offended at all if you decide to bail!