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

Summary Table with numerous dimensions compared

I need to create a summary table to compare Customers to their respective regions, markets and overall by the potential of what each customer has purchased in the current fiscal year.

Here's an example that might explain it better.

CustomerPotential by MarketPotential by RegionPotential Overall
ABC602050
DEF202030
TOTAL307060

So that is how I would like my finished table to look.

The problem is with calculating the potential.

The way they want the potential calculated is to ONLY count the potential if the Region/Market/Overall average of sales per commodity is greater than the specific customer average of sales per Commodity. I already have this table set up in one of the tabs, but it does not summarize and it set to calculate based on the selection of the Customer. See example below.

With Customer ABC selected

CommodityCustomer AvgMarket AvgMarket PotentialRegion AvgRegion PotentialOverall AvgOverall Potential
Apples10201040206050
Oranges308050100200
Bananas50400200100
TOTAL602050

As you can see, the totals from this chart's potential, matches the summary chart. That is basically what I need to do. But every time I try to do a formula for it, it just sums the total of everything minus the total of everything and then gives me 0 for most things if the total customer avg sales is greater than the total avg sales for region or market or overall.

I have tried using Aggr() functions but it doesn't want to exclude the negative values like I need it too.

I've even considered creating the Avgs in a table and putting the values in a field that I can work with.

Any help would be greatly appreciated!

3 Replies
swuehl
MVP
MVP

Advanced aggregation is probably the way to go, something along the lines (example for Market Potential):

=Sum( Aggr( YourSecondTableMarketPotentialExpression, Customer, Commodity))

crystles
Partner - Creator III
Partner - Creator III
Author

I tried that before, but it just gives me $0 for all Commodities.

Here is the formula for my potential in the second table.

Num($(vMarket_SalesAvgAnnualized)-$(vAvgSalesPerCustomer) )
<=0,'',
Num($(vMarket_SalesAvgAnnualized)-$(vAvgSalesPerCustomer) )))

swuehl
MVP
MVP

And what is the definition of your variables?

I think it would be easier to understand your setting and help if you could post a small sample QVW? Would this be possible?