topic Re: Current Year and Prior Year Gross Amount Calculation in QlikView

Current Year and Prior Year Gross Amount Calculation

AH — Mon, 09 Feb 2015 15:32:49 GMT

Hi,

I have Gross amount calculation different for pror to 2015 and in 2015. The filtering should be based on invoice year.

The Calculation i have in 2015 is:
=num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}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'}>}LineAmount),'##,##0')) ,'##,##0')

And Prior to 2015 is:
=num(num(Sum({<SalesType = {'SalesRegister', 'FreeText', 'Service'}>}LineAmount),'##,##0')-

num(Sum({<GLAccount={101100,115100,115110,115120,225430,420020},SalesType={'FreeText'}>}LineAmount),'##,##0'),'##,##0')

How to i put the Invoice year in these two piece of expression that will show me the gross amount Prior to 2015 and In 2015 only?

I appreciate your help.

Thanks,

Shan

Re: Current Year and Prior Year Gross Amount Calculation

Anonymous — Mon, 09 Feb 2015 16:05:50 GMT

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 mis-type...

Re: Current Year and Prior Year Gross Amount Calculation

AH — Mon, 09 Feb 2015 16:13:03 GMT

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

AH — Mon, 09 Feb 2015 16:18:59 GMT

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

Anonymous — Mon, 09 Feb 2015 16:27:06 GMT

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

AH — Mon, 09 Feb 2015 18:30:50 GMT

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

Anonymous — Mon, 09 Feb 2015 18:45:14 GMT

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

AH — Mon, 09 Feb 2015 18:54:08 GMT

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

AH — Tue, 10 Feb 2015 15:35:10 GMT

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

Re: Current Year and Prior Year Gross Amount Calculation

Anonymous — Wed, 11 Feb 2015 17:23:51 GMT

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