- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Subtract an aggregation with record
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
Any one can help this.
Thanks
Sundar
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What is the number that you expect to see as a output?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you try this:
Sum(m1*internal) - Min(TOTAL <name> Aggr(Sum(m1), name, exam)*internal)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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