Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
Creator
Creator

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
rubenmarin

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?

View solution in original post

8 Replies
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
Creator
Creator
Author

thank you for your time, none of these expressions work

rubenmarin

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
Partner - Creator III
Partner - Creator III

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

brilliant, than you so much