How to calculate a portfolio and group by month

[#1]

Hello hello,

at first, you can find enclosed my sample app for this problem and also the csv-file with the relevant data.

In our app we have 3 KPIs/measures:

The fist "New Acquisitions" shows the started contracts of a time period - in our case all started contracts of 2014 - since 2014 is selected.

The second measure "Ended Contracts" shows - naturally - all ended contracts of the same time period, i.e. again of 2014.

Now, "Portfolio" is supposed to be the total amount of currently active contracts (i.e. started in 2014 or before and End Date >2014). For December 2014 this figure would be 15 (4 started contracts in 2013 plus 15 new contracts in 2014 minus 4 ended contracts of 2014). As a total figure this works fine.

However, if we display "Portfolio" in a bar chart, we only get to see the deltas vs. the previous month (unfortunately also aggregated, i.e. 2013 and 2014 deltas combined):

Here is what the bar chat is supposed to look like (taken from Excel):

How can we solve this problem?

Maybe it is also possible to simplify our calculation for the portfolio measure?!

[#2]

Hey Targa,

Have a look at the app attached.

Regards

RL

[rubenmarin]

Hi Targa, you can use this expression:

Count(TOTAL {$<CanonicalYear = {"<$(=(CanonicalYear))"},

CanonicalMonth = ,

DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<CanonicalYear = {$(=(CanonicalYear))}, DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

, 0, Num(CanonicalMonth)))

-

(

Count(TOTAL {$<CanonicalYear = {"<$(=(CanonicalYear))"},

CanonicalMonth = ,

DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<CanonicalYear = {$(=(CanonicalYear))}, DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

, 0, Num(CanonicalMonth)))

)

[#4]

Thx a lot, Ruben!

This works brilliantly - at least for a monthly development bar chart.

Now, for another chart (same TestApp.qvf) we need the yearly development of the portfolio - as shown in the example below (Excel):

How do we need to adjust the expression to get the result required?

Can we change the current measure or do we need to create a new one just for this special purpose?

Thanks a lot in advance for your support.

[rubenmarin]

Hi, I don't know how to do with years over 2014, but those two years give me those results using:

Count(TOTAL {$<CanonicalYear = {"<$(=Min(CanonicalYear))"},

CanonicalMonth = ,

DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

, 0, RowNo()))

-

(

Count(TOTAL {$<CanonicalYear = {"<$(=Min(CanonicalYear))"},

CanonicalMonth = ,

DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

, 0, RowNo()))

)

[#6]

Hi Ruben,

thanks again for your input. This solved our problem.

However, now all available years are shown in our bar chart (see example below, not taken from TestApp.qvf):

As far as we know, you can limit/restrict your columns in Qlik Sense. But we were only able to limit a certain amount of columns, e.g. 10 years, from the left or the right. The example below shows 10 years from the left:

This is not satisfactory, because we need a specifc range of years - for example 2009 to 2015.

Is there a way to achieve this, possibly even derived by the current year (i.e. Year(Today())?

[rubenmarin]

Hi Targa, I don't have access to my Sense computer now but you can try to use a calculated dimension, something like:

Aggr(If(CanonicalYear>=2009 and CanonicalYear<=2015, CanonicalYear), CanonicalYear)

You can create variables in script to limit this year in only one spot to quickly change limit in all graphs (also I think there is an extension to change variable values outside script), I give you script approach... add variables in script:

LET vMinYear=2009;

LET vMaxYear=2015;

And use calculated dimension like:

Aggr(If(CanonicalYear>=$(vMinYear)and CanonicalYear<=$(vMaxYear), CanonicalYear), CanonicalYear)

Hope this helps you.

[#8]

Hi Ruben,

thanks for your post and sorry for my late response. I was out of the office for a few days due holidays here in Germany.

Your dimension looks simple and logical but unfortunately it doesn't work. When I use it as dimension instead of the MasterYear field, I get no data to display.

[rubenmarin]

Hi, when I use Aggr(If(CanonicalYear>=2014 and CanonicalYear<=2018, CanonicalYear), CanonicalYear)

Returns data with years 2014 and 2018 (mine has no data in 2015, 2016 and 2017), so dimension is filtered as expected.

Limiting the min year shown in calculated dimension has impact in the expression, you should use the min year used in the dimension to calculate the start portfolio, as Min(CanonicalYear) will not be the first showed in the graph:

Count(TOTAL {$<CanonicalYear = {"<2014"},

CanonicalMonth = ,

DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<DateType = {'Contract_Start'}>}DISTINCT(Contract_Key))

, 0, RowNo()))

-

(

Count(TOTAL {$<CanonicalYear = {"<2014"},

CanonicalMonth = ,

DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

+

Rangesum(Above(

Count ({<DateType = {'Contract_End'}>}DISTINCT(Contract_Key))

, 0, RowNo()))

)

[#10]

Hi Ruben,

thanks again for your input!

I have tested your solution with our real data / real app and it is working quite well.

I have defined two variables and I have used them in the dimension and in the calculation of the measure.

Now, it is working very good if you select no year. But if you select a year the calculation is of course wrong.

Is it possible to ignore the selected year and display all the years like defined in the two variables?

E.g.:

MinYear is 2009

MaxYear is 2015

You select 2014 as Year.

Then the chart should nevertheless display the data for the years 2009 to 2015. Independent from the selected year.

I hope you or any other member cann help me with this final "problem"

Have a great day!