Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
AH
Contributor

Current Year and Prior Year Gross Amount Calculation

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

Tags (1)
9 Replies
mov
Esteemed Contributor III

Re: Current Year and Prior Year Gross Amount Calculation

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

AH
Contributor

Re: Current Year and Prior Year Gross Amount Calculation

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

AH
Contributor

Re: Current Year and Prior Year Gross Amount Calculation

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

mov
Esteemed Contributor III

Re: Current Year and Prior Year Gross Amount Calculation

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.

AH
Contributor

Re: Current Year and Prior Year Gross Amount Calculation

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

mov
Esteemed Contributor III

Re: Current Year and Prior Year Gross Amount Calculation

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?

AH
Contributor

Re: Current Year and Prior Year Gross Amount Calculation

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

AH
Contributor

Re: Current Year and Prior Year Gross Amount Calculation

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

mov
Esteemed Contributor III

Re: Current Year and Prior Year Gross Amount Calculation

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

Community Browser