11 Replies Latest reply: Apr 14, 2015 7:51 AM by Denis Windisch RSS

    How to calculate a portfolio and group by month

      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):

      WrongChart.jpg

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

      RightChart.png

       

      How can we solve this problem?

       

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

        • Re: How to calculate a portfolio and group by month
          Rahul Lakhina

          Hey Targa,

           

          Have a look at the app attached.

           

          Regards

           

          RL

          • Re: How to calculate a portfolio and group by month
            Ruben Marin

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

            )

              • Re: How to calculate a portfolio and group by month

                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):

                barchartexample.png

                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.

                  • Re: How to calculate a portfolio and group by month
                    Ruben Marin

                    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()))

                    )

                      • Re: How to calculate a portfolio and group by month

                        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):

                         

                        screen.jpg

                         

                        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:

                        screen2.jpg

                        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())?

                          • Re: How to calculate a portfolio and group by month
                            Ruben Marin

                            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.

                              • Re: How to calculate a portfolio and group by month

                                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.

                                  • Re: How to calculate a portfolio and group by month
                                    Ruben Marin

                                    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()))

                                    )

                                      • Re: How to calculate a portfolio and group by month

                                        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!

                                          • Re: How to calculate a portfolio and group by month
                                            Ruben Marin

                                            Hi Targa, sorry for delay but I'm quite busy these days.

                                             

                                            You can use a dimension ignoring selections, something like:

                                            Aggr(If(Only({1} CanonicalYear)>=2014 and Only({1} CanonicalYear)<=2018, Only({1} CanonicalYear)), CanonicalYear)

                                             

                                            And you should add the 'ignore selections condition' in the expression (The '1' in Set analysis):

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

                                            CanonicalMonth = ,

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

                                            +

                                            Rangesum(Above(

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

                                            , 0, RowNo()))

                                            -

                                            (

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

                                            CanonicalMonth = ,

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

                                            +

                                            Rangesum(Above(

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

                                            , 0, RowNo()))

                                            )

                                             

                                            Can be debugged to ignore only the year but I'm not sure if that's what you want and also I don't have time to develop.

                                             

                                            Hope this helps!