topic Re: Show totals in Straight Table Chart in QlikView

Show totals in Straight Table Chart

Tue, 20 Jan 2015 01:23:07 GMT

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?

Re: Show totals in Straight Table Chart

Gysbert_Wassenaar — Wed, 21 Jan 2015 14:09:18 GMT

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.

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 14:20:40 GMT

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

Re: Show totals in Straight Table Chart

Gysbert_Wassenaar — Wed, 21 Jan 2015 14:27:05 GMT

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.

Re: Show totals in Straight Table Chart

Anonymous — Wed, 21 Jan 2015 14:30:31 GMT

" 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.

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 14:38:40 GMT

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

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 14:41:04 GMT

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

Re: Show totals in Straight Table Chart

Gysbert_Wassenaar — Wed, 21 Jan 2015 14:52:05 GMT

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.

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 14:54:52 GMT

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

Re: Show totals in Straight Table Chart

Gysbert_Wassenaar — Wed, 21 Jan 2015 14:56:02 GMT

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

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 15:00:16 GMT

Wow! I didn’t realize Qlikview had such features. In case you haven’t guessed, I’m very new to QV. My Company brought it a few months ago, but it was the “baby” of one executive. AS of a few weeks ago, I became the default QV guru. I understand basics bit I’m getting up to speed on advanced functions, expressions, etc. I really appreciate the help and advice.

Sincerely,

Bernard Smith | SQL Report Developer

Re: Show totals in Straight Table Chart

Anonymous — Wed, 21 Jan 2015 15:06:56 GMT

See chart properties in the attached file, tab Expressions. Notice Conditional check box and the condition below it.

Also notice the nature of expression itself - I used only(Amount) by purpose so it doesn't return expression total unless only one location is selected.
The way is to use sum(Amount), there will be no need for this trick. This is what's Gysbert Wassenaar means. I'm sure there is a way to fix your expression.

Re: Show totals in Straight Table Chart

Wed, 21 Jan 2015 15:45:57 GMT

Michael, Gysbert, Thank you for the advice. I'm going to take your suggestions and work with them for a bit. I'm also reviewing my load script and overall data model. I'll upload a copy of my app later today with results. Again, I thank you both for the valuable advice. I hope to hear from you again!