topic Re: Total in a Gauge chart in QlikView

Total in a Gauge chart

vvvvvvizard — Sun, 28 Jun 2015 14:52:15 GMT

i have a straight table with the dimension Name :

expression 1 = Sum(A)

expression 2 = Sum(B)

expression 3 = Sum(C)

expression 4 = if(SumA>0,Sum(A),

if(SumA>0,Sum(B),

if(SumA>0,Sum(C))

Now i need the get the total of expression 4 in a Gauge chart .

to work out the total , for each "Name" if it has a value in A add it to the total

if it has a value in B add it to the total

if it has a value in C only add it to the total if A or B has a value or 0 or is blank

Name cannot have a value in A and B at the same time

Name can have a value in C and either A or B at the same time

I couldnt upload my QVW so i just created a similar scenario with fake data , the Total CPU column total of 20 needs to match the total in the Gauge chart , thanks you .

Message was edited by: yusuf rawat

Re: Total in a Gauge chart

maxgro — Sun, 28 Jun 2015 15:31:09 GMT

sum(aggr(Sum(A)+Sum(B)+if(sum(A)=0 or Sum(B)=0, sum(C),0), Name))

Re: Total in a Gauge chart

MarcoWedel — Sun, 28 Jun 2015 21:10:37 GMT

Hi,

your expression

=if(SumA>0,Sum(A),if(SumA>0,Sum(B),if(SumA>0,Sum(C))

will only return Sum(A) if SumA>0, else it returns null.

Is this a typo?

please post sample data and expected result to clarify.

regards

Marco

Re: Total in a Gauge chart

jagan — Mon, 29 Jun 2015 02:24:54 GMT

Hi,

If you attached some sample data and your expected output then it would be easier to understand and provide the solution. As per your explanation please find below expression

=Sum(Aggr(Sum(A) + Sum(B) + If(Sum(C) AND (Sum(A) > 0 OR Sum(B) >0), Sum(C),0), Name))

OR simply

=Sum(A) + Sum(B) + Sum(C) (AS you have mentioned that if it has a value in C only add it to the total if A or B has a value or 0 )

Hope this helps you.

Regards,

Jagan.

Re: Total in a Gauge chart

vvvvvvizard — Mon, 29 Jun 2015 11:08:55 GMT

That was a typo

=if(Sum(A])>0,Sum(A),

if(Sum(B)>0,Sum(B),

Sum(C)

))

Re: Total in a Gauge chart

vvvvvvizard — Mon, 29 Jun 2015 13:44:35 GMT

I have attached some sample data

Re: Total in a Gauge chart

maxgro — Mon, 29 Jun 2015 14:16:02 GMT

sum

(

aggr(

if(sum(A)>0,sum(A),

if(sum(B)>0,sum(B),

if(sum(A)=0 and sum(B)=0, sum(C)

))),

Name)

)

Re: Total in a Gauge chart

sunny_talwar — Mon, 29 Jun 2015 14:24:34 GMT

you just need this to get a total of an expression in a text box object

Sum(Aggr(YourExpressionfromtheTable, YourDimensionIntheTable))

Using the above logic, this seems to work:

=Sum(Aggr(if(Sum(A)>0,sum(A),

if(Sum(B)>0 , sUM(B),

Sum(C))), Name))