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)
where EFF_START_DT <= getdate()
and year(EFF_START_DT) >= 2003