topic Sum not appearing for Expressions with before Function in QlikView

Sum not appearing for Expressions with before Function

Tue, 22 Mar 2011 10:13:45 GMT

Hi All,

I have a pivot table with following expression,

Before(

sum((-1*(Val* pick(wildmatch(Type,'A', 'B','*'),$(ADi)/100, $(BDi)/100, 1)))+Val )
,
pick(wildmatch(Type,'A', 'B','*'),$(AMv), $(BMv), 0)
)

where i am moving a defined percentage of field to next N column using variables, now i need to show the total of the this column in the bottom using partial sum, but its not working, any idea ?

Thanks in advance

Jeswanth

Sum not appearing for Expressions with before Function

Wed, 23 Mar 2011 08:09:01 GMT

Just learned about adding code snippet to forums 🙂 thought of explaining the same again with the new tags.

i have a pivot table where i do what if analysis where every dimension will have a percentage and a delay, so i add pct and delays and create a what if scenario using following code



Before(
SUM((-1*(Val* pick(wildmatch(Type,'A', 'B','*'),$(ADi)/100, $(BDi)/100, 1)))+Val )
,
pick(wildmatch(Type,'A', 'B','*'),$(AMv), $(BMv), 0)
)

and this expression dont create partial sums but if i try teh same without before function which is used for delays the partial sum works fine like teh following


sum((-1*(Val* pick(wildmatch(Type,'A', 'B','*'),$(ADi)/100, $(BDi)/100, 1)))+Val )

any idea why the above dont calculate sum ?

it will great if some one can help me out here.

Jeswanth

AW:Sum not appearing for Expressions with before Function

brenner_martina — Wed, 23 Mar 2011 08:35:17 GMT

Hi,

the pivottable needs a function for the sum, that's why you need to aggregate your expression by one or more dimenions:

Sum(Aggr(your_expression),Dimension1,Dimension2,...))

AW:Sum not appearing for Expressions with before Function

Wed, 23 Mar 2011 09:57:01 GMT

Thanks Martina,

I tried that alredy and it comes for the second code snippet above, but when i try teh same for first one with Before it returns Zero. Any idea if we need to take care when use Before function ? see what i tried below


Sum(
Aggr(
Before(
Sum((-1*(Val* pick(wildmatch(Type,'A', 'B','*'),$(ADi)/100, $(BDi)/100, 1)))+Val )
,
pick(wildmatch(Type,'A', 'B','*'),$(AMv), $(BMv), 0)
),Type))

I have a sample qvw file, but not seeing any option to share teh same here. at least you can see a screen shot here.

This one before using Aggr Function

Jeswanth