Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

liviumac
New Contributor III

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

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


1 Solution

Accepted Solutions

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

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?

8 Replies

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

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
New Contributor III

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

thank you for your time, none of these expressions work

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

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?

raju_insights
Contributor III

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

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
New Contributor III

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

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
New Contributor III

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

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

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

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
New Contributor III

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

brilliant, than you so much

Community Browser