topic Re: Subtract an aggregation with record in QlikView

Subtract an aggregation with record

Tue, 27 Sep 2016 09:31:50 GMT

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

Re: Subtract an aggregation with record

Thu, 06 Oct 2016 11:17:00 GMT

Any one can help this.

Thanks

Sundar

Re: Subtract an aggregation with record

sunny_talwar — Thu, 06 Oct 2016 17:18:57 GMT

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

Re: Subtract an aggregation with record

Thu, 06 Oct 2016 17:56:05 GMT

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

Re: Subtract an aggregation with record

sunny_talwar — Thu, 06 Oct 2016 18:04:07 GMT

Can you try this:

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

Re: Subtract an aggregation with record

Mon, 10 Oct 2016 11:44:41 GMT

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

Re: Subtract an aggregation with record

sunny_talwar — Mon, 10 Oct 2016 15:51:42 GMT

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