
Re: Subtracting in Set Analysis...???
Stefan Wühl Jun 25, 2012 7:00 PM (in response to Syed Khaled Shahbaaz)The expression looks ok to me at first glance. Are your Year and Amount fields located in the same table?
Could you post a small sample together with your expected result?

Re: Subtracting in Set Analysis...???
Syed Khaled Shahbaaz Jun 25, 2012 7:44 PM (in response to Stefan Wühl )Hi,
Yes, the fields are from the same table. Here's the sample file for better insight.
Also, what would be my approach for the same if fields were from different tables?
Regards,
Khaled.

Cars_Test.qvw 163.8 K

Re: Subtracting in Set Analysis...???
Stefan Wühl Jun 25, 2012 8:12 PM (in response to Syed Khaled Shahbaaz)Khaled if you want to subtract sum of amount for certain years, I think you should just use
=sum({<Year = {2010,2011}>} Amount)  sum({<Year = {1999}>} Amount)
If you use something like
=sum(
{
<Year={2010,2011}{1999}>
}
Amount)
This is not calculating the sum of amount for years 2010 and 2011 minus the sum of amount for year 1999, as you noticed. It is defining a selection in field Year, setting years 2010 and 2011 and excluding 1999 (but this is never part of 2010, 2011 anyway). So you will always only get the sum of amount for the (in the set expression) set years 2010 and 2011, i.e. 1500.
I am sorry, I haven't spent enough attendance to your initial requirement ("to get total amount for the current selected year and sum of amount for 2005,2012 minus the sum of amount for the year 1999."): you might get the correct result with your original expression (including the current selection) only if the excluded year is part of that current selection (this is what also the sample in the HELP says).
Hope this helps,
Stefan

Re: Subtracting in Set Analysis...???
Syed Khaled Shahbaaz Jun 26, 2012 9:08 AM (in response to Stefan Wühl )Thanks Stefan, I am using =sum({<Year = {2010,2011}>} Amount)  sum({<Year = {1999}>} Amount) already but had it (sum({<Year={2010,2011}{1999}>}Amount) ) worked, I think the calculations would have been much lighter than what I otherwise have using the original expression since I have used similar calculations in multiple instances.
Regards,
Khaled.

Re: Subtracting in Set Analysis...???
Stefan Wühl Jun 26, 2012 10:21 AM (in response to Syed Khaled Shahbaaz)Khaled,
I just don't think that you can create a set that does what you want: subtracting values.
In a set expression field modifier you define which field values are used to filter your results, but it's just selected / not selected.
And results for nonselected field values will just not be taken into account (i.e. not added up, but also not subtracted).
If it's all about achieving a higher maintainability, I think you could look into using variables.
Regards,
Stefan



