11 Replies Latest reply: Aug 21, 2018 12:06 PM by David Forest

# Inforce count - Insuaranc

Hello,

I am trying to calculate the inforce count using the link -  https://community.qlik.com/message/497885#497885

And I am unable to work out the set expression for LIVE, and DROPPED.

Dimensions: CalendatMonthAndYear

Expressions:

Started =count({<DateType={'Start'}>} distinct Policy)

Expired/Cancelled =count({<DateType={'End'}>} Policy)

Live =rangesum(Above(Live),Started)   -  This gives me Badfield name - Live,Started.

Dropped =rangesum(Above(Dropped),[Expired/Cancelled])

InForce =Live-Dropped

Errors:

Live =rangesum(Above(Live),Started)

The above expression gives an error as  Badfield name - Live,Started.

similarly

Dropped =rangesum(Above(Dropped),[Expired/Cancelled])

The above expression gives an error as  badfield name - Dropped,[Expired/Cancelled].

• ###### Re: Inforce count - Insuaranc

Make sure you're using the correct case sensitive field names.

• ###### Re: Inforce count - Insuaranc

When referencing a Qlik variable to get its value you need to \$ expand it.

Live =rangesum(Above(\$(Live)),\$(Started))

Without the \$() Qlik assumes a name refers to a Field in the data.

• ###### Re: Inforce count - Insuaranc

May be this:

Rangesum(above(count(Live),0,Started))

Rangesum(above(count(Dropped),0,[Expired/Cancelled]))

• ###### Re: Inforce count - Insuaranc

Not working

• ###### Re: Inforce count - Insuaranc

Post formulas, screen of results does not help.

Are you referencing your variables with \$()?

• ###### Re: Inforce count - Insuaranc

My expressions are as below

Started =count({<DateType={'Start'}>} distinct Policy)

Expired/Cancelled=count({<DateType={'End'}>} Policy)

Live =rangesum(Above(\$(Live),\$(Started))

Dropped  =rangesum(Above(\$(Dropped)),\$[Expired/Cancelled])

Inforce =\$(Live)-\$(Dropped)

• ###### Re: Inforce count - Insuaranc

Live references itself with no apparent initial value, it will be null

• ###### Re: Inforce count - Insuaranc

so what is the way to work around?

could you give me a detailed explanation

• ###### Re: Inforce count - Insuaranc

Instead of referencing Live in Live, you need to based it on the field that has your data.

To get more help, you'd have to post sample data and expected outcome.

• ###### Re: Inforce count - Insuaranc

Rangesum(above(TOTAL SUM(Live),0,Started))

Rangesum(above(TOTAL Sum(Dropped),0,[Expired/Cancelled]))

Better post the screenshot, of Live and Dropped Fields from the script.

• ###### Re: Inforce count - Insuaranc

Hello,

Many thanks to everyone who looked into this.

I am adding logic in an expression, and not in the script.

you could see all of them are ZERO's under Live variable.

Could you please let me know what is that iIam doing incorrectly here.