Expression on aggregated data

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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]))

Best,

Sunny

[Anonymous]

Hi Sunny,

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

[sunny_talwar]

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