table / chart issue QV v8.5

[#1]

Hi All,

Have an issue where 2 subtotals in the same table are behaving differently to the rest of the subtotals, and don't know why?

Have amended first thoughts to enable sub-totals to appear, but they don't look correct, nor do they appear in the charts?

Have attached an example file

Any ideas greatly appreciated!

Kind regards,

Rich

(also please let me know if the values are being displayed in your version (if different to v8.5)

[pover]

Use the following formula:

sum({$<Year = {$(=max(Year))}, Material={"=sum({$<Year = {$(=min(Year))}>} Euros)=0"} >} Euros)

Regards.

[#3]

Many thanks for your support Karl, works a treat!

Kind regards,

Rich

[#4]

Hi Karl,

Having reviewed your formula further, it did correct the "Materials In" subtotal. When applying/modifying the formula to "Materials Out", the subtotal / row data was only returning one of the 6 values that it should have.

Found another way to use my existing formulas, and to get the subtotals working (ie changing to sum of rows).

However, using this approach, I can correct all of my tables to display correctly, but cannot get any values to be displayed on the 2 charts.

Any thoughts?

Kind regards,

Rich

[#5]

"Materials In" & "Materials Out" in particular

[#6]

Have added a revised file, detailing what I am trying to achieve in each of the fields...

Appreciate any thoughts/comments...

Kind regards,

Rich

[pover]

Rich,

Here's a new formula

-sum({$<Year = {$(=min(Year))}, Material= E({1<Year={$(=max(Year))}>}) + {"=sum({$<Year = {$(=max(Year))}>} Euros)=0"}>} Euros)

which basically means to consider Material that was excluded from any transaction during the max year or had transactions that summed 0 euros in the max year.

It seems to work fine in your graph, too.

Regards.

[#8]

Hi Karl,

Thanks for your response!

Using your revised formula, I am not getting the results that you state you are getting (ie revised formula in my version (QV v8.5) is returning "-" in all instances)

Unsure if this is a bug with QV8.5, or there is an issue with the formula?

Have updated the file, creating a "Mats Test" col

Kind regards,

Rich

[matt_crowther · Accepted solution]

Rich,

Solution attached - hopefully; it works for me in v9.

Again it's 'Set Analysis Aggregation' to the rescue:

The 'Materials In' formula:

=

sum(aggr((if(sum({$<Year = {$(=min(Year))}>} Euros)=0,sum({$<Year = {$(=max(Year))}>} Euros),0)),Material))

The issue is due to the lack of a dimension on the chart - in the table each line is being passed through the 'if' expression and can pass or fail accordingly. Setting the Total Mode to 'Expression Total' fails as the 'whole' fails the 'if' and therefore returns 0, 'Sum of Rows' works in the table as it's doing exactly that; summing the individual lines that have either passed or failed the 'if' - I would have expected that to work in the chart.

So the soution works by using the 'aggr()' function to put each line (Material) through the 'if' and then sum the results - just as the table does with 'Sum of Rows' so we get the same results.

I found the easiest way to test this was to drop the expression into a Text box and get that to match.

I'd recommend having a look at aggr() as it's really useful in these more complex situations.

Hope that helps, any further issues let me know.

Matt - Visual Analytics Ltd

[#10]

Hi Matt,

Once again many thanks for your tip, this small change makes a big difference!!

Also, thanks for the explanation, "give a man a fish..."

Kind regards,

Rich