topic table / chart issue QV v8.5 in QlikView

table / chart issue QV v8.5

Mon, 14 Feb 2011 17:41:30 GMT

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)

table / chart issue QV v8.5

pover — Mon, 14 Feb 2011 20:42:10 GMT

Use the following formula:

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

Regards.

table / chart issue QV v8.5

Tue, 15 Feb 2011 11:32:01 GMT

Many thanks for your support Karl, works a treat!

Kind regards,

Rich

table / chart issue QV v8.5

Tue, 15 Feb 2011 12:30:36 GMT

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

table / chart issue QV v8.5

Tue, 15 Feb 2011 12:32:15 GMT

"Materials In" & "Materials Out" in particular

table / chart issue QV v8.5

Tue, 15 Feb 2011 17:57:18 GMT

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

Appreciate any thoughts/comments...

Kind regards,

Rich

table / chart issue QV v8.5

pover — Wed, 16 Feb 2011 00:49:02 GMT

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.

table / chart issue QV v8.5

Wed, 16 Feb 2011 10:41:09 GMT

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

table / chart issue QV v8.5

matt_crowther — Wed, 16 Feb 2011 12:15:28 GMT

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

table / chart issue QV v8.5

Wed, 16 Feb 2011 14:50:21 GMT

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

table / chart issue QV v8.5

Wed, 16 Feb 2011 17:17:46 GMT

Hi Matt / All,

Am trying to apply the "Aggr" function to overcome similar problems found in earlier example.

This formula is only a little more complex than the past, although am unsure where exactly the " aggr((( " " ),Material) " ought to be placed in this example:

The below formula returns data as it should in a table, now need to modify it so that its "Graph" friendly...

______________________________________

if(sum({$<Year = {$(=min(Year))}>} QtyKG)=0

sum({$<Year = {$(=max(Year))}>} QtyKG)=0

(sum({$<Year = {$(=max(Year))}>} QtyKG/1000)
-
sum({$<Year = {$(=min(Year))}>} QtyKG/1000))
*
sum({$<Year = {$(=max(Year))}>} Euros/1000)/sum({$<Year = {$(=max(Year))}>} QtyKG/1000)

___________________________________

Initial thoughts were to only have one "sum(Aggr((...." & " ....),Material)) " at the begining or the end, but did not give the desired results.

Tried putting these statements after the "IF" statement, and wasn't seeing what I hoped to

Also tried adding "Aggr((" & "),Material))" in each of the formulae that orginal just had a "sum("

Anyone with any thoughts?

Kind regards,

Rich

table / chart issue QV v8.5

Wed, 16 Feb 2011 19:17:15 GMT

please ignore the above, I was missing a ")". Solution works fine with the "sum(Agg(( " & "),Materials))