business-intelligenceqlikviewqliksense

QlikSense, how to make a piechart where the value for the pie chart is the sum of multiple summed columns, and the slices are the columns


Image of Table

I am new to Qliksense and having trouble with a pie chart.

I have a table that has 11 columns of different material losses on each day of a conflict. I need to make a pie chart of 8 of the different materials and their individual contribution to the whole of the material losses.

Day Tanks Helecoptor APC
1 3 1 4
2 5 3 0
3 10 2 12

100% of my pie chart would be the sum of all the values of all three(or 8) columns. Then I want to display the sum of each individual columns as the slices. So the total of the 3 sample columns is 40. Then the 'tanks' slice of the pie would sum its column, 18, and show that as the percentage of the total pie, %45.

And so on for each of the 3(or 8) different columns in the pie chart.

I think I need to make the each of the needed columns the dimension, to set what needs to be the slice names, then identify the sums of those columns and percentage those out as the measures.

I just cannot figure out how to do it.

I have tried adding the columns to the measure and applying a 'RangeSum' to all the needed columns of the sheet. This clearly led to the range of the values, not the sum of them.

I have also tried adding the individual columns to the dimensions with the 'Sum' function. Whenever I try to add Sum(columnName), 'Invalid Dimension' is displayed.

I have the raw values, but I want the pie chart to be interactable with the reat of the sheets, so I can't just input '=x' to the function.


Solution

  • In QlikView you would have been able to directly do it the way you want to.

    In Sense you will need to change how the data is read into the data model as Sense will not accept that many measures in a Pie chart

    LOSSES:
    crosstable(Type,Data,1)
    load * inline [
    Day, Tanks, Helecoptor, APC
    1,   3,     1,          4
    2,   5,     3,          0
    3,   10,    2,          12];
    

    Then you can sum(Data) with Type as the dimension and get this pie

    Pie chart

    Or for extra credit you can do a stacked bar and show the losses per day if you wanted to Daily Stacked Bar

    If you don't have access to the script and can't change the way the data is loaded you could fake it by using stacked bars in a combo chart using this expression sum(APC)/sum(APC+Tanks+Helecopter) and the dimension '=1'

    Pretend Pie