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

[#2]

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?

[swuehl]

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.