
Re: Current Year and Prior Year Gross Amount Calculation
Michael Solomovich Feb 9, 2015 11:05 AM (in response to Shan Ahmed)If I understand you correctly, this should work:
=num(
Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount)

(
Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount)
+
if(Year=2015,Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}>}LineAmount),0)
)
,'##,##0')I made the part with ItemId conditional depending on Year. Also removed unnecessary num() functions for easier read.
Please count my open/close parenthesis, it is easy to mistype...

Re: Current Year and Prior Year Gross Amount Calculation
Shan Ahmed Feb 9, 2015 11:13 AM (in response to Michael Solomovich )Michael,
Thanks for your reply. The Current year apply for everything in this calculation. So, In that case i apply the 'If' logic for the whole expression right?
Also,
How can i calculate the gross amount the prior to 2015 year?
Appreciate your help!
Thanks,
Shan

Re: Current Year and Prior Year Gross Amount Calculation
Shan Ahmed Feb 9, 2015 11:18 AM (in response to Shan Ahmed)is this expression i am trying is incorrect?
=
num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'},[Invoice Year]={$(=Year(Today()))}>}LineAmount),'##,##0')(
num(Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),'##,##0') + num(Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}, [Invoice Year]={$(=Year(Today()))}>} LineAmount),'##,##0')) ,'##,##0')
Thanks,
Shan

Re: Current Year and Prior Year Gross Amount Calculation
Michael Solomovich Feb 9, 2015 11:27 AM (in response to Shan Ahmed)Technically it looks correct. Logically  I can't tell, it depends on what you need. Can only tell what it is doing:
Of the three parts, the 1st and the 3rd are calculated only for the current invoice year, and the 2nd for all years. If it is what you need, it is correct.

Re: Current Year and Prior Year Gross Amount Calculation
Shan Ahmed Feb 9, 2015 1:30 PM (in response to Michael Solomovich )Michael,
I was trying with your expression and i am getting zero values for this expression. I think i am doing wrong here.
=
num(
if([Invoice Year]<=2015,Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount),0)

(
if([Invoice Year]<=2015,Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),0)
+
if([Invoice Year]<=2015,Sum({<ItemId={'GW_CLX','GW_XTS','CR_L','CR_U'}, SalesType={'SalesRegister'}>}LineAmount),0)
)
,'##,##0')Thanks,
Shan

Re: Current Year and Prior Year Gross Amount Calculation
Michael Solomovich Feb 9, 2015 1:45 PM (in response to Shan Ahmed)Well, it doesn't look like my expression. It is logically your first expression for 2015 but with condition:
if("Invoice Year"<=2015, <your expression>, 0)
Given that today all years are <=2015, it should work as your expression.
Can you upload a small example of your application, so I (or anyone else) can take a look?

Re: Current Year and Prior Year Gross Amount Calculation
Shan Ahmed Feb 9, 2015 1:54 PM (in response to Michael Solomovich )Michael,
Sorry if i misinterpret it incorrectly. I didnt mean its your expression. I was trying to say that i was trying to use yours and came up with the mentioned expression.
Thanks,
Shan

Re: Current Year and Prior Year Gross Amount Calculation
Shan Ahmed Feb 10, 2015 10:35 AM (in response to Michael Solomovich )Hi Michael,
I just uploaded a piece of my allication. Could you please have a look at it and suggest/Advise how i can achieve what i want in the application?
The Gross amount Calculation is different in year 2015 than prior to year 2015.So when i want to calculate gross amount in 2015 i just want only the year 2015 gross amount. But when i mean prior to 2015 then all the year prior to 2015 gross amount should populate. And I would be able to filter based on year and by customer as well.
The way i am trying to calculate is not working the way i want it to be.
Highly appreciate your help!
Thanks,
Shan

SR.qvw 244.0 K

Re: Current Year and Prior Year Gross Amount Calculation
Michael Solomovich Feb 11, 2015 12:23 PM (in response to Shan Ahmed)Hi Shan,
I got time to take a look.
The 2015 expression is incorrect because of the wrong field names  it should be [Sales Type] instead of SalesType, and ItemID instead of ItemId. The "prior to 2015" looks fine, and works as you described.
I do have a question about the logic though. It maybe correct or not... How the record should be counted if it fits in more than one part of the expression? In the other way, the expression:
sum(<set1> Amount) + sum(<set2> Amount)
may returns different result then
sum((<set1>+<set2>) Amount)
In the first case, if a record is included in both set1 and set2, it will be counted twice. In the second case, it will be counted only once. If no any record can be in both sets, than it doesn't matter what approach to use.
Regards,
Michael






