10 Replies Latest reply: Jan 31, 2018 8:59 AM by omar bensalem RSS

    Bar chart in Qliksense

    John Smith

          Hi all,

       

      I would need help on the stacked bar chart in qliksense.

       

      I have 3 columns of data i.e Department, Revenue Original, Revenue Estimated.

      Now i have to calculate revenue ratio i.e revenue original / revenue estimated -

      who calculate the percentage (revenue original/revenue estimate) / total of both sum and if the ratio is <1 make it revenue failure if not revenue failure.

      and we need to count the number of revenue failure and revenue pass percentage.

      those percentage graph should be on bar chart .

       

      Ex is attached in the image and source spread sheet is also attached - please help me out

        • Re: Bar chart in Qliksense
          John Smith

          any help

          • Re: Bar chart in Qliksense
            Mohan Kumar

            Hi,

             

            Use this extension from branch, I found much useful for 100% Stacked bar chart.

            Qlik Branch

            • Re: Bar chart in Qliksense
              omar bensalem

              would it be sthing like this?

              Capture.PNG

                • Re: Bar chart in Qliksense
                  John Smith

                  thanks for this detailed explanation but however for each department we need to find out how many fail or pass not on whole departments-

                  hope you understand if not please let me know

                  each department wise i need how many failure or pass percentage --

                  i,e for each department consider 8 pass and 4 failure (so pass % would be around 60-70% and failure would be 30%)

                   

                  hope this can be done and let me know

                • Re: Bar chart in Qliksense
                  omar bensalem

                  or like this?

                  Capture.PNG

                   

                  To achieve this:

                   

                  1) Use a bar chart

                   

                  2) as a dimension :  Department

                   

                  3) as measures:

                   

                  Original % Total:

                  Sum([Revenue Original]) / (sum({<Department>}[Revenue Estimated])+sum({<Department>}[Revenue Original]))



                  Estimated % Total:

                  Sum([Revenue Estimated]) / (sum({<Department>}[Revenue Estimated])+sum({<Department>}[Revenue Original]))

                   

                  4) Under aspect > General : add a title as follow:

                  ='Nbr of Departments to PASS : '&(count({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))>1"}>}Department)) &' | Nbr of Departments to FAIL : '&(count({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))<=1"}>}Department))

                    • Re: Bar chart in Qliksense
                      omar bensalem

                      You can also add as a subtitle:

                      ='Departments to PASS : '& concat

                      ( aggr(only({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))>1"}>}Department),Department),', ')

                      &

                      ' | Departments to FAIL : '& concat

                      ( aggr(only({<Department={"=(Sum([Revenue Original]) / sum([Revenue Estimated]))<=1"}>}Department),Department),', ')


                      to have also the list of departments that pass and fail:

                       

                      Capture.PNG

                    • Re: Bar chart in Qliksense
                      omar bensalem

                      You can also add yet another bar chart; to see how much was done by department compared to estimated:

                       

                      Dimension: Department

                      Measure: Sum([Revenue Original]) / sum([Revenue Estimated])

                       

                      and under aspect> colors > color by expression:

                      if(Sum([Revenue Original]) / sum([Revenue Estimated])>1,green(),red())


                      Result:

                      Capture.PNG

                      • Re: Bar chart in Qliksense
                        omar bensalem

                        If u want to sort the 2 charts, by the percentage of realisation (original % estimated):

                         

                        1) in the first chart; under the sort tab: put the measure above the dimension:

                        Capture.PNG

                        2) in the second bar chart; under the sort tab; sort the dimension Department by expression (descending):

                        Sum([Revenue Original]) / sum([Revenue Estimated])

                         

                        Capture.PNG

                        result:

                        Capture.PNG

                        • Re: Bar chart in Qliksense
                          John Smith

                          thanks for this detailed explanation but however for each department we need to find out how many fail or pass not on whole departments-

                          hope you understand if not please let me know

                          each department wise i need how many failure or pass percentage --

                          i,e for each department consider 8 pass and 4 failure (so pass % would be around 60-70% and failure would be 30%)

                           

                          hope this can be done and let me know