topic Re: Counting in an expression based on dimension not in chart in QlikView

Counting in an expression based on dimension not in chart

Tue, 15 Jul 2014 15:35:14 GMT

I have a chart right now that has one dimension (lets say it's [Profit Center]) and an expression that is defined as:

sum([Standard Gross Margin]) / sum([Net Sales]). This gives me a chart by Profit Center of a percentage. Looks great.

However, now I want to add another expression to this chart. For each Profit Center, I want to have a count of when a Customer's percentage who is "in" that [Profit Center] (defined the same as above, but done by Customer not Profit Center) is greater than the percentage of a profit center.

Example Data:

Profit Center Expression %

A 80

B 50

C 20

Example Data with Customer included:

Profit Center Customer %

A 1 79

A 2 83

A 3 90

B 4 30

B 5 51

C 6 25

In the chart, we would see 3 data points of Profit Center A, B and C with 80,50 and 20 graphed respectively. Then I would want a split axes of 2, 1 and 1 graphed since the count of Customer A's percentage that is above 80 is 2 etc.

The data examples above are not individual tables, just an example of how we want to calculate the %. The data for each expression resides in one table.

I've tried Aggr and other functions to no avail.

Re: Counting in an expression based on dimension not in chart

veidlburkhard — Tue, 15 Jul 2014 16:08:24 GMT

Hi Adam,

you can do it like this. See the picture below

Hope this helps

Burkhard

Re: Counting in an expression based on dimension not in chart

veidlburkhard — Tue, 15 Jul 2014 16:13:29 GMT

Sorry Adam, could not upload the picture.

So I will explain the solution.

Add a second epression: Concat(If(% > Expression%,Customer), ',')

Uncheck the 'Bar' check box and mark the 'Values on Datapoints'. For your first expression make sure that the 'Values on Datapoints' check box is not marked.

This gives you the number of outstanding customers on top of the bars that show the expression% of the Profit Centers.

Hope this helps

Burkhard

Re: Counting in an expression based on dimension not in chart

Tue, 15 Jul 2014 16:28:42 GMT

Thanks for the reply Burkhard.

I think part of the trouble I'm having is being the expression that calculates the % by customer to compare it to the original % by Profit Center.

I don't actually have that % shown in the second table. I basically need to calculate that in the expression and then compare it to the expression used to calculate % by Profit Center. That make sense?

Re: Counting in an expression based on dimension not in chart

veidlburkhard — Tue, 15 Jul 2014 18:54:17 GMT

Hi Adam,

I think in that case you have to substitute 'Expression%' by your expression. But I'm not sure if you really want this. Cause in your chart the only thing that is shown are the names of the outstanding customers, then!?!

A straight table would be much more adequate for this scenario...

Burkhard