How to count # of subscriptions

[#1]

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...

[orital81]

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

[swuehl]

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

[#4]

try this

count(Distinct subscriptions)

count(Distinct expirations)

[#5]

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..

[#6]

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.

[swuehl · Accepted solution]

Try chart inter record functions, something along these lines:

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

See also attached.

[#8]

Thx swuehl, that was very helpfull.

[#9]

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?

[swuehl]

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.