topic Re: Subtract same fields data in QlikView

Subtract same fields data

stephenedberkg — Wed, 19 Aug 2015 10:03:44 GMT

I have the Transaction type it contain Credit,invoice ,FIS and FIR

i want to subtract Invoice-Credit that is my sales.

i wrote like this

=sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'INVOICE'}>}#InvNetSales) -

sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'CreditNote'}>}#InvNetSales)

i Got the sales values but i want some easy way to implement this formula.

thanks in advance

Re: Subtract same fields data

gautik92 — Wed, 19 Aug 2015 10:08:25 GMT

create individual variables for both invoice and credit then you can use it in your expression

Re: Subtract same fields data

stephenedberkg — Wed, 19 Aug 2015 10:14:10 GMT

Yeah that is also one way.

=Sum({<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},TransactionType={'CreditNote'}- {'INVOICE'}>}#InvNetSales)

Is it write way to write the formula like above?

Re: Subtract same fields data

sunny_talwar — Wed, 19 Aug 2015 10:17:15 GMT

I don't think this will work. I think what you have initially is the only front end method to do it.

Re: Subtract same fields data

gautik92 — Wed, 19 Aug 2015 10:19:59 GMT

Dear Stephen Edberkg ,

It wont work I hope

Re: Subtract same fields data

stephenedberkg — Wed, 19 Aug 2015 10:24:53 GMT

need some alternative way

Re: Subtract same fields data

stephenedberkg — Wed, 19 Aug 2015 10:26:01 GMT

Do you have any other methods

Re: Subtract same fields data

sijojoseph22 — Wed, 19 Aug 2015 10:31:05 GMT

As a first step you can use vairable for '$(=Max(Year))}' and $(=Max(MonthID))} which will simply your formula little further.

Second step you can create field '#InvNetSales' for 'INVOICE' and 'CREDIT' seperatly in the script and you can apply this field directly the the fomula. This will help to remove the 'TransactionType' from the Set Analysis.

Re: Subtract same fields data

gautik92 — Wed, 19 Aug 2015 10:34:24 GMT

in the Script itself create two fields for sum of Invoice and credit then use it in the formula but creating variables is quite easy

Re: Subtract same fields data

sasiparupudi1 — Wed, 19 Aug 2015 10:41:36 GMT

may be you can use a parameterised variable here.Please check out the following post

https://community.qlik.com/docs/DOC-9045

hth

Sasi