Need help in Expression

sona_sa — Tue, 21 Apr 2015 04:08:34 GMT

Hi,

I have an object, where I am Calculating Segment wise amount. There is one field, Name is Total ODE. It is nothing just the Sum of (ODE Class A Item + ODE Class B Item + Advertisement & Sales Promotion).

Now the problem is where I am writing an expression the value is coming wrong. Reason, It is calculating (ODE Class A Item + ODE Class B Item + Advertisement & Sales Promotion) + (Exchange (Gain) / Loss + Bad Debts).

i.e. – (0.6 + 0.1 + 0.0) + (- 0.2 + 0.0) = 0.5 which is wrong.

Correct value is (0.6 + 0.1 + 0.0) = 0.7.

My Expression is =

if(Match($(CurrentMonth),1),
if(match(Summury_Line_No,390), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),21))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),17)),
if(match(Summury_Line_No,400), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),22))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),14)),
if(match(Summury_Line_No,410), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),23))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),11)),
if(match(Summury_Line_No,380), (((above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),24) )+(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),25)))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),27)))*100,
(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]))/100000)
)))
,
if(match(Summury_Line_No,390), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),21)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),21))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),17)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),17)),
if(match(Summury_Line_No,400), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),22)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),22))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),14)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),14)),
if(match(Summury_Line_No,410), (above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),23)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),23))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),11)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),11)),
if(match(Summury_Line_No,380), (((above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),24) - above(sum({<[Display Year]={$(PreviousYear)}, [Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),24))+(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),25)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),25)))/(above(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance]),27)-above(sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]),27)))*100,
(sum({<[Display Year]={$(PreviousYear)}>} [Monthly Balance])-sum({<[Display Year]={$(PreviousYear)},[Display Month Name]={$(PreviousMonth)}>} [Monthly Balance]))/100000)
)))
)

For more ref. please find the attached xls.

Thanks in advance.

Re: Need help in Expression

sona_sa — Wed, 22 Apr 2015 06:22:34 GMT

Any expectation from anyone.

topic Re: Need help in Expression in QlikView

Need help in Expression

Re: Need help in Expression