Subtract same fields data

[stephenedberkg]

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

[gautik92 · Accepted solution]

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

[stephenedberkg]

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?

[sunny_talwar]

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

[gautik92]

Dear Stephen Edberkg ,

It wont work I hope

[stephenedberkg]

need some alternative way

[stephenedberkg]

Do you have any other methods

[sijojoseph22]

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.

[gautik92]

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

[sasiparupudi1]

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

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

hth

Sasi