topic Re: Getting expression total on the first row based on an IF statement in QlikView

Getting expression total on the first row based on an IF statement

Fri, 06 Nov 2015 08:37:30 GMT

Hey Guys,

I just created an expression based on an IF statement but I can't get the SUM total on the first row even though the expression total is selected on the Expression tab. Please any idea on how to figure this out will be truly appreciated.

Thanks,

Simon.

Re: Getting expression total on the first row based on an IF statement

swuehl — Fri, 06 Nov 2015 13:28:19 GMT

And your expression looks like ...?

If your if() statement is embedded in the aggregation, I think it should work.

If your if() statement is testing dimension values, outside the aggregation, you need to use advanced aggregation to get the total:

=Sum( Aggr( If(Dim ='A', Sum(Value1), Sum(Value2) ), Dim))

Re: Getting expression total on the first row based on an IF statement

alexandros17 — Fri, 06 Nov 2015 13:32:25 GMT

are you using a pivot table ?

If yes, try to transform to a straight table and verify the result, if this is the case (otherways let me know) you have to use dimensionality finction

Re: Getting expression total on the first row based on an IF statement

Fri, 06 Nov 2015 13:39:05 GMT

Hi ,

I am using a straight table but its not given me the total on the first row.

Re: Getting expression total on the first row based on an IF statement

Fri, 06 Nov 2015 13:46:58 GMT

Hi ,

my expression is as follows:

if(column(3) <0,0.25 * column(3)).

It give me the sum total on the individual fields but not the grand total on the first row. If I add sum to the expression, it show the sum total on the first row as 0.

Re: Getting expression total on the first row based on an IF statement

swuehl — Fri, 06 Nov 2015 14:02:43 GMT

And the expression in column(3) looks like...

Re: Getting expression total on the first row based on an IF statement

Fri, 06 Nov 2015 14:08:29 GMT

Column(1) : Sum(Distinct )Column(2) : Sum( + + )

So column(3) : Column(1) - Column(2).

Re: Getting expression total on the first row based on an IF statement

swuehl — Fri, 06 Nov 2015 14:16:35 GMT

Sorry, your posted expressions don't make sense to me.

Could you upload a small sample QVW that demonstrates your issue?

Re: Getting expression total on the first row based on an IF statement

Fri, 06 Nov 2015 14:27:05 GMT

Hi, Sweuhl,

I have figure it out. Thanks for your contribution all thesame.

Re: Getting expression total on the first row based on an IF statement

gsbeaton — Fri, 06 Nov 2015 14:42:01 GMT

Hi Simon,

Try putting the the function column(3) on its own into your expression, is it reproducing the desired result? There is no reason why your expression shouldn't work, even with an IF. Total's should also work.

You are testing for values less than 0 (ie, negative values) so your expression is only going to fire if the value in column 3 is negative. Is this what you meant? Sorry if this is vague, there's not a lot to go on!

If you can post your QVW I'd be happy to take a look.

George