Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

tomtastic
New Contributor II

Expression on aggregated data

Dear Qlik community,

I recently started using Qlik Sense desktop as Excel could no longer handle the amount of data I was feeding it. While I really like the performance I’m struggling a bit with the expressions. Therefore I registered to this community to get some help.

Please have a look at the visual overview of the requirement I put in attachment. You’ll see I added a stacked bar chart where I already used an expression to display the percentage of each Buying Channel per Year/Month.

I then added a second chart (= KPI) where I would like to have the average percentage of a sum of Buying Channels displayed. E.g. when Year/Month 2015/01, 2015/02, 2015/03 is selected it displays 25,33% and when only 2015/01 and 2015/02 is selected it displays 25,00%.

I tried using the following expression: AVG(Count({<[Buying Channel]={Single PO}>}[Buying Channel])+Count({<[Buying Channel]={Low Value}>}[Buying Channel]))

Unfortunately this expression is not valid. Any ideas?


P.S. I also attached the qvf-app.

 

 

Tags (1)
1 Solution

Accepted Solutions

Re: Expression on aggregated data

Try this expression:

Avg(Aggr(Sum({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}Aggr(Round(Count([Buying Channel])/Count(TOTAL <[Year/Month]>[Buying Channel]), 0.01), [Year/Month], [Buying Channel])), [Year/Month]))

But this has gotten very complicated because of the need to round the numbers to reach what you are looking for.

If rounding is not a big requirement, then below will work as well:

Avg(Aggr(

Count({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}[Buying Channel])/Count([Buying Channel]), [Year/Month]))

Capture.PNG

Best,

Sunny

3 Replies

Re: Expression on aggregated data

Try this expression:

Avg(Aggr(Sum({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}Aggr(Round(Count([Buying Channel])/Count(TOTAL <[Year/Month]>[Buying Channel]), 0.01), [Year/Month], [Buying Channel])), [Year/Month]))

But this has gotten very complicated because of the need to round the numbers to reach what you are looking for.

If rounding is not a big requirement, then below will work as well:

Avg(Aggr(

Count({<[Buying Channel] = {'eCatalog', 'Mat. Master Contract'}>}[Buying Channel])/Count([Buying Channel]), [Year/Month]))

Capture.PNG

Best,

Sunny

tomtastic
New Contributor II

Re: Expression on aggregated data

Hi Sunny,

Exactly what I was looking for. You even went "overboard" with the rounding, but it is much appreciated. Thx.

Re: Expression on aggregated data

Well the issue was the without rounding I was not getting the same number as yours and I was not sure if those are the exact numbers you are looking for or not. But I am glad we got it resolved

Community Browser