11 Replies Latest reply on Mar 1, 2017 6:55 AM by Norbert Maijoor

# Customers above average - LOD expression

Hello again, community!

I want to create a bar chart showing the number of customers who have above average number of records in the data set.

Now, I've already calculated that number (1161, in orange), but now I don't know how to display it in a bar chart. I know it involves a LOD expression and I don't want you to just spit me the solution, I want you to tell me what my reasoning should be, as I want to get there myself.

I can't attach the workbook as it keeps saying the file is too large...

Thank you!

Esther

• ###### 1. Re: Customers above average - LOD expression

Hi Esther,

It's not entirely clear what you're trying to achieve. Do you just want a single bar that goes up to the number 1,161? Can you draw a picture of what you'd like your end result to look like?

Christina

• ###### 2. Re: Customers above average - LOD expression

Morning Esther,

Oke first the tea & biscuits..... then a "why" how you would like to "vizcuit" your average;) and what your are aiming for...

• ###### 3. Re: Customers above average - LOD expression

Good morning, Christina Gremore and Norbert Maijoor!

Tea for everyone!

The solution goes like this:

But if you think there is a better way to display this number, please do make a case about it

Thank you!

Esther

• ###### 4. Re: Customers above average - LOD expression

Hi Esther,

Did the tea & picked the biscuit;) Find my approach as reference in attached workbook version 9.3 located in the original thread.

But I think you should also review the standards for business communications

1 of 1 people found this helpful
• ###### 5. Re: Customers above average - LOD expression

Norbert,

I was actually looking for a solution that would make use of the current status of the view (through LOD expression), not a hard-coded solution on the data source. I couldn't attach the workbook bu it is from this challenge workbook (advanced tab - customers above average). I know it already provides the solution but I did not glance at it as I was hoping someone would show me the light without needing to.

But thanks for your reference on that very useful link to standards for business communication. It was an unknown association to me until this very moment!

Esther

• ###### 6. Re: Customers above average - LOD expression

Have a good day!

• ###### 7. Re: Customers above average - LOD expression

Goodmorning Esther,

Oke needed another tea & biscuits but still not "getting it"  what you are aiming for:(. Apologizes for that;).

I checks the challenge workbook but not able to download. Only in PDF.

Hope to hear from you

• ###### 8. Re: Customers above average - LOD expression

Hello Norbert!

No need to apologize; if there is a misunderstanding then the fault is always on the sender side

So, I want to display that 1161, but not in a hard-coded way - I want to get it up front using a LOD expression (FIXED). But not only that; I want the reasoning behind this approach. I mean, I want to know what levels of granularity are we playing with.

Thanks!

Esther

• ###### 9. Re: Customers above average - LOD expression

Good afternoon Esther,

Oke here we go!;)The following......

I defined first the avg of the dataset based on LOD expression: avg sales: {fixed:avg([sales])}

Defined the Customers which are above avg: if {fixed [Customer]:sum([sales])}>=[avg sales] then 1 END

In sheet 1 I could have used sum([sales])>=[avg sales] because Customer is also available and ensures the right "aggregation level" at Customer level

In sheet 2 I also need the aggregation level customer to be able to calculate but only would like the amount as a KPI so LOD comes in and I defined the calculation as mentioned above based on LOD {fixed [Customer]:sum([sales])}

Hope this is what you are looking for and look forward receiving your feedback at return:)...it's time for another biscuit...

Regards,

Norbert

1 of 1 people found this helpful
• ###### 10. Re: Customers above average - LOD expression

Hello Norbert,

that's exactly what I was looking for!

Another biscuit for you

Esther

• ###### 11. Re: Customers above average - LOD expression

Hi Esther,

You are welcome. Thanks for the "Badges". Appreciated:)