Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

crystles
Contributor 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
MVP
MVP

Re: Summary Table with numerous dimensions compared

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

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

crystles
Contributor III

Re: Summary Table with numerous dimensions compared

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

MVP
MVP

Re: Summary Table with numerous dimensions compared

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?

Community Browser