Show totals in Straight Table Chart

[#1]

Hi,

I have a table chart as illustrated below:

The chart allows drill down to data for specific dates. I'm calculating some of the columns. My problem is some of the calculated columns do not give a grand total when at the highest level of the chart, but if I drill down the column correctly totals the column. Can anyone advise what I'm missing in my qvw?

[Gysbert_Wassenaar]

It's possible that an expression cannot be calculated at the total level, i.e. without the dimensions. In a straight table you can set the Total Mode to Sum of Rows to sum up the values of the individual rows. Perhaps that is enough to solve your problem.

[#3]

Thank you for replying. I’ve used the sum of rows option. The problem is when I drill down the sum of rows option results in an overstated total. I think the behavior is a result of trying to mix summary and detail level data in the same chart. Can you recommend a way of doing this? Is there a function or expression in Qlikview that can distinguish when to sum rows and when to use the default expression total?

Sincerely,

Bernard Smith | SQL Report Developer

[Gysbert_Wassenaar]

Without understanding what you're actually trying to do I can only make very wild guesses. Perhaps you need to use the aggr function to sum the rows: sum(aggr( ...expression... , ChartDimA, ChartDimB,...,ChartDimX). Perhaps you can use the dimensionality function. Perhaps you need to use the getcurrentfield function.

[Anonymous]

" Is there a function or expression in Qlikview that can distinguish when to sum rows and when to use the default expression total?"

No, but you can create two identical expressions with different total mode, and use conditional show - show one with expression total on one condition, and another with sum of rows on another condition.

[#6]

Thank you for your “Wild” Guesses. They actually sound like great options that I need to research. I’ve already started looking into AGGR and I’ll check the others as well. Also, I apologize if my request seems cryptic. I’m trying not to overload readers with detail. However, if it will help, I’ll give a brief description.

I’m developing a sales dashboard (sourced from a SQL Server Query), which compares daily sales activity with monthly sales quotas across company branches.

My initial approach for the straight table chart is one column which shows the total sales YTD by region and Total Quota YTD by region. I developed the table so users initially see all regions, then can drill down to the daily activity. The quota column uses a calculation to determine the daily quota for the region based on the monthly quota and planned number of working days.

The thing I don’t understand is why at the region level of the straight table, the daily sales provides an accurate total automatically, but the daily quota is a null value. However, if I drill into the dates, the daily quota total is calculated .

Can you offer any advice on why the quota column as described above does not provide a total at the highest level of my “rollup”?

Sincerely,

Bernard Smith | SQL Report Developer

[#7]

Thank you for the reply. This sounds like what I need to do. Can you suggest a resource that describes with examples how to do what you’ve suggested?

Sincerely,

Bernard Smith | SQL Report Developer

[Gysbert_Wassenaar]

Can you offer any advice on why the quota column as described above does not provide a total at the highest level of my “rollup”?

Not without first looking at the qlikview app.

[#9]

Thank you. I’ll upload the app for reference, but I’ll need a little time to prep it before upload.

Sincerely,

Bernard Smith | SQL Report Developer

[Gysbert_Wassenaar]

No problem. Perhaps this document can help with that: Preparing examples for Upload - Reduction and Data Scrambling