topic Re: How to count # of subscriptions in QlikView

How to count # of subscriptions

Tue, 23 Jul 2013 13:17:36 GMT

Hello,

I just started to work with qlikview and am still in a learning curve...

I've a question about counting the # of available subscriptions in comparison with the # of expirations.

In my table I have year(expirationdate) as dimension.

For each year I want to see the # of expirations and the # of subscriptions which are left.

So for instance :

year subscriptions expirations

2013 100000 10000

2014 90000 12000

2015 78000 8000

2016 70000 etc...

With Count({1} subscription) i'm getting the 10.000, 12000 and 8000 for the respective year.

But how do I get the # of subscriptions?

To be more specific I added a test file:

There are 12 subscriptions. In 2014 2 will expire, so I'd like to see in 2015 at the # of subscriptions column 10 subscriptions.

In 2018 - 7, in 2021 - 6 etc...

Re: How to count # of subscriptions

orital81 — Tue, 23 Jul 2013 13:27:24 GMT

Hi tbeenders,

Do you have subscription Id and Expiration Id?

when Using Count it is suggested to use Count(Distinct [Unique ID])

So, for both subscription Id and Expiration the expression should be similar:

Count(Distinct [subscription ID])

Count(Distinct [expression ID])

Does this help?

If not - it will be easier to help you if you attach your example

Re: How to count # of subscriptions

swuehl — Tue, 23 Jul 2013 13:28:17 GMT

Please check this solution (I assume it's matching your setting and requirement)

Re: How to count # of subscriptions

Tue, 23 Jul 2013 13:38:04 GMT

try this

count(Distinct subscriptions)

count(Distinct expirations)

Re: How to count # of subscriptions

Tue, 23 Jul 2013 13:42:31 GMT

Hi Ori,

Thanks for the quick reply.

I dont have a subscription Id and Expiration Id. I have a subscription Id and a expiration date.

And to count the # of subscriptions I need to know if the subscription hasnt expired yet.

So the 90000 subscriptions of 2014 are the total number of subscriptions -/- the one which expired in 2013.

And the # of subscriptions in 2015 are the total number of subscriptions -/- expirations 2013/2014.

Etc..

Re: How to count # of subscriptions

Tue, 23 Jul 2013 13:45:08 GMT

Sorry Vishwaranjan,

This doesnt work as I dont have seperate fields for them. A subscription is still valid if the expiration date is not met.

See above where i explained it to Ori.

Re: How to count # of subscriptions

swuehl — Tue, 23 Jul 2013 14:18:15 GMT

Try chart inter record functions, something along these lines:

=count({$} TOTAL Polisnummer) - RangeSum(above(count({1} Polisnummer),1,rowno()-1))

Re: How to count # of subscriptions

Wed, 24 Jul 2013 07:57:40 GMT

Thx swuehl, that was very helpfull.

Re: How to count # of subscriptions

Fri, 26 Jul 2013 10:05:02 GMT

Hmm I thought I was going on the right track, but when I added months as a dimension I got confused again...

It works fine if you dont drill down to the months, but if you go to the months level it starts each year with the same total again.

E.g.:

On year dimenstion:

2013 90 subscriptions , 4 expirations

2014 86 subscriptions , 1 expiration

2015 85 subscriptions , 0 expirations

2016 85 subscriptions , 16 expirations

ETC..

On month dimension:

2013 90 subscriptions , 4 expirations

2014 90 subscriptions , 1 expiration

2015 90 subscriptions , 0 expirations

2016 90 subscriptions , 16 expirations

What should the expression look like to get the months correctly?

Re: How to count # of subscriptions

swuehl — Fri, 26 Jul 2013 10:30:11 GMT

above() will only consider dimension values in the same column segment, that's why you get a reset at year bounderies. You can use the TOTAL qualifier to get around this.

Re: How to count # of subscriptions

Fri, 26 Jul 2013 11:13:18 GMT

Hi Swuehl,

Thx for the quick reply, but it seems that in your example the years are now messed up. Is there a possibility to have both?

Re: How to count # of subscriptions

swuehl — Fri, 26 Jul 2013 11:46:25 GMT

I am probably missing something more simple.

The numbers should be right with month values all collapsed or all expanded, it seems you get an issue with partial expanded months.

You can get what you want using advanced aggregation. To make this work, your dimensions year and month needs to be in chronological load order.

Since your initial load doesn't fulfill this requirement, I stored your table into a QVD and started from scratch.

Please check attached.