Help creating a line chart with shares as measure - Qlik Sense

[liviumac]

Hello again

I have the following data structure

field 1 = date (several dates)

field 2 = customer type (several customer types for each date)

field 3 = customer id (many per customer type)

field 4 = transaction id (many per customer)

field 5 = Value (several per transaction id)

I want to create a line chart (or another visualization) that has:

- as dimensions: date (on X axis) and customer type (each customer type shown in the chart as a line)

- as measure: the share of [sum(Value) (per date and customer type)] in [sum(Value) per date]

essentially I am trying to show a pie chart with customer type shares for each date, but for all the dates at once, using one line for each customer type

the sum of shares (percentages) of customer type for each date should obviously be 100%

or, in other words, I want to disregard the customer type dimension when calculating the denominator, but not the date dimension

I have tried

1. sum(aggr(sum(value),date,customer_type) / sum(aggr(sum(value),date)

but it does not show meaningful data

2. sum(aggr(sum(value),date,customer_type) / sum(TOTAL aggr(sum(value),date)

it shows meaningfull data, but it divides by the total sum of Value in the database, so does not take into account the date

3. sum(aggr(sum(value),date,customer_type)/sum(aggr(sum(TOTAL value),date)

but it does not show meaningful data

your help is highly appreciated

thank you

LM

[rubenmarin]

try this:

sum(aggr(sum(value),date,customer_type) / sum(aggr(sum(TOTAL value),date)

or:

sum(aggr(sum(value),date,customer_type) / sum(aggr(sum(TOTAL <date> value),date)

[liviumac]

thank you for your time, none of these expressions work

[rubenmarin · Accepted solution]

OK, if you have customer_type and date as dimensions maybe you only need:

Sum(value)/Sum(Total <customer_type> value)

If doesn't works... can you upload a sample?

[rajuamet]

Hi Liviu Maco,

Try this in line chart (not in combo chart),

Dimension 1 - date

Dimension 2 - customer type

Measure -  sum(Value) / sum(total Value)

[liviumac]

thank you, this last one did the trick!

well with one change, i.e. Sum(Total <date> value) instead of Sum(Total <customer_type> value), I think this is what you wanted to write, no?

I apologize if I abuse your time, but I have 2 questions:

1) I've not seen before the <field> qualifier without { } brackets - what does it mean?

i.e., what is the difference between

Sum(Total <customer_type> value)

and

Sum(Total {<customer_type>} value)

2) the chart now works, however I would like to tweak it further in the sense that I want it to show the same share also when I select one customer types - now it shows 100%

thank you

[liviumac]

hello

thank you for your answer - the denominator in your expression is calculated over the whole data set, i.e. for all dates, so it is not what I need

Sum(Total <date> value) does the trick, as indicated by Ruben Marin

[rubenmarin]

1) TOTAL is used to ignore chart dimensions but not selections

In "Sum(Total <customer_type> value)" the "<customer_type>" tells to TOTAL to not ignore the customer_type dimension (as you said it should be 'date', not customer_type)

In "Sum(Total {<customer_type>} value)" the "<customer_type>" is inside '{}' wich means a set analisys expression, in this case you are applying a set analisys telling to ignore selections in customer_type

2) Maybe:

Sum(value)/Sum(Total <date> {<customer_type>} value)

[liviumac]

brilliant, than you so much