Calculating SUM (Debal - write off- recovery)

[#1]

Hi,

I need to calculate the below balance based on Min Year start and Max Year start . the  date format is in the MMM-YYYY form. I'm writing the below syntax. I know somewhere is wrong. can some one please guide me in writing the new syntax. I'm new to qlik.

= sum({<new_date={">=$(vYearStart))<=$(vYearMax)"},(sum(DBAL)-sum(WRITE_OFF)-sum(SECURED_RECOVERY)))

Thanks in advance.

Sowjanya

[prieper]

think that - provided the formatting of your variables vYearStart and vYearMax is correct - you do not need the preceeding "SUM" in the individual expressions, thus

= sum({<new_date={">=$(vYearStart) <= $(vYearMax)"}, DBAL - WRITE_OFF - SECURED_RECOVERY) should do.

In case of some NULL-values, you may write NUMSUM(DBAL, - WRITE_OFF, - SECURED_RECOVERY)

Peter

[#3]

Hi,

Peter thanks for responding to my question.

I tried the other way around by creating 2 set analysis expression and it worked. below is the syntax I used.

=Num((sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}DBAL)- sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>} WRITE_OFF)
-sum({<new_date={">=$(vYearStart))<=$(vYearMax)"}>}SECURED_RECOVERY))/1000000,'£###' & ' ' &'mn')

thanks

Sowjanya