6 Replies Latest reply: May 12, 2018 1:35 PM by bhasker kumar RSS

    sum function

    Bassam Frem

      Hello friend

       

      I have the below two tables:

       

      1/ Facts Table:

      collateral_idcollateral_amount
      1$200
      2$200
      3$200

       

       

      2/ Ageing Table

      bucketcollateral_idage
      June/2017130
      June/2017255
      June/2017360
      July/2017125

       

       

      In the visuals I want to have the following output (by bucket)

       

      bucketAmountAge 0-30Age 30-60
      June/2017200+200+200=600200200
      Jul/20172002000

       

      for the amount: sum(collateral_amount)

      for the Age: sum({<age={">0<30"}> collateral_amount}

       

      Problem & question:

      the result is correct only when i select the dimenstion in the output table. the result is incorrect if i do not select the dimension

      So when i select June/2017, the measure "Amount, Age0-30 and Age 30-60" and recalculated and they become correct.

       

      How can i obtain the correct result without the need to select the dimension ?

      (note that it is not possible to connect merge the two table in a single one)

       

      Thanks

        • Re: sum function
          Akshaya Aditya

          Hi Bassam,

           

          Please find attached application on the data you shared. Hope this helps.

           

          Regards,

          Akshaya

            • Re: sum function
              Bassam Frem

              hi Akshaya,

              Tk you

              how can i import qlikview app to qlik sense ?

                • Re: sum function
                  Akshaya Aditya

                  Hi Bassam,

                   

                  You can use the logic used in the app to your Qliksense.

                  I have created a extra column in data using class function and using it in the expression.

                   

                  Script :

                  LOAD * INLINE [

                    collateral_id, collateral_amount

                      1, 200

                      2, 200

                      3, 200

                  ];

                  T:

                  Load * INLINE [

                  bucket, collateral_id, age

                  June/2017, 1, 30

                  June/2017, 2, 55

                  June/2017, 3, 60

                  July/2017, 1, 25

                  ];

                   

                   

                  NoConcatenate

                  TF:

                  LOAD *,class(age,31,'Age') as AgeGroup Resident T;

                   

                   

                  Drop Table T;

                   

                   

                  Front End


                  Expression - sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 0 - 30

                                       - sum({<AgeGroup = {'0 <= Age < 31'}>}collateral_amount) for Age 31 - 62


                   

                  Hope this helps.

                   

                  Regards,

                  Akshaya

              • Re: sum function
                bhasker kumar

                Hi,

                 

                Please find below script and Expression which was used to calculate the desired result.

                 

                Note: I haven't linked and marge the two tables.

                 

                Data Load Script:

                FactsTable:

                Load * inline [

                collateral_id1, llateral_amount

                1,$200

                2,$200

                3,200

                ];

                 

                ageing_table:

                Load * inline [

                bucket,collateral_id,age

                June/2017,1,30

                June/2017,2,55

                June/2017,3,60

                July/2017,1,25

                ];

                 

                2. On Chart Expression i have used calculated dimension in order to populate the bucket

                Dimension :=if(match(collateral_id,collateral_id1),bucket)

                Expression: Age 0-30 :  sum({< age = {">0 <= 30 "}>}llateral_amount)

                                  Age 30 -60: sum({< age = {">30 <=60 "}>}llateral_amount)

                chart.PNG

                 

                if you found this helpful please mark helpful or correct.

                 

                Regards,

                Bhasker Kumar

                  • Re: sum function
                    Bassam Frem

                    Thank you bhasker it worked

                    Can you explain what is the rationale ? what was wrong and how you fixed it ?

                    Is this the only way?

                    I would have like to keep the two table connected through collateral_id

                     

                    thanks

                      • Re: sum function
                        bhasker kumar

                        Hi,

                         

                        You can connect both the table through collateral_id.

                         

                        I haven't faced any issue just used in line load and required expression, there may be issue with the data formatting connected dimension or used dimensions in your chart.

                         

                        for the age bucking you can either use front expression that i have used or in back-end you can use Class function.

                         

                        i have used calculated dimensions: if(match(collateral_id,collateral_id1), bucket) , match function return true when first table collateral_id values matched collateral_id1 then it will return the corresponding bucket  in dimension.

                         

                        if you found this helpful, please mark correct or helpful.

                         

                        Regards,

                        Bhasker Kumar