Subtract an aggregation with record

[#1]

Hi,

I have an requirement to do  a subtraction with an aggregation

Eg:

consider  i have this record .

name,exam,qtr,m1,internal

xxxx,Monthly,q1,10,1

xxxx,Monthly,q1,20,2

xxxx,Monthly,q1,30,3

xxxx,Monthly,q1,40,4

xxxx,Monthly,q1,50,5

My Expression should be

sum(m1-min(m1)) * internal

What i have done is i created one expression

MinM1 -> min(m1)

then i call this expression in my final expression like this

sum(m1-MinM1) * internal

but it's return in null.

i agree i'm doing something stupid but this is expected behavior from my client end.

Can some one help me . I attached my Qlikview file

[#2]

Hi

Any one can help this.

Thanks

Sundar

[sunny_talwar]

What is the number that you expect to see as a output?

[#4]

Hi Sunny,

in chart

i want the result like this

Rowno    name ,        exam ,    qtr,   expr

1               xxxx ,            Monthly,  q1     400 

400 = >(10-10)*1 + (20-10)*2   ..etc

for this records

name,exam,qtr,m1,internal

xxxx,Monthly,q1,10,1

xxxx,Monthly,q1,20,2

xxxx,Monthly,q1,30,3

xxxx,Monthly,q1,40,4

xxxx,Monthly,q1,50,5

Thanks in Advance

Sundar

[sunny_talwar]

Can you try this:

Sum(m1*internal) - Min(TOTAL <name> Aggr(Sum(m1), name, exam)*internal)

[#6]

Hi Sunny,

Sorry for my late response due to War  the communication got disconnected here .

Thanks for your command .

I forget to mention thats It's Ad-hoc report so it's should be work on different dimension and filter selection

But when i remove name Field in chart the result little different i found the aggr function take only for name,exam  this cause issue

attached my expected result in Excel and Qlikview file. But the result will vary based on dimension selection and Filter selection .

This excel is for only name dimension

Thanks in Advance

[sunny_talwar]

I think it would be helpful if you can lay out for us few different scenarios with there expected results. How are you adding/removing dimensions (in your ad-hoc report)? Using the technique specified here -? Customizable Straight Table? or do you it differently. Provide some example so that we can help you better