Expression based on row cells in chart

midwest — Fri, 02 Jun 2023 21:56:44 GMT

Hi,

I am trying to take the sum of the projected incurred claims from Jan 2022-Dec 2023, and divide it by the sum of the FFS membership values from Jan 2022-Dec 2023.

Here is the expression for the "12 month ending dec" field that I am using in the straight table chart:

if(MonthName(service_month)>=MonthName(AddMonths(today(),-17))
and MonthName(service_month)<=MonthName(AddMonths(today(),-6)),
sum(TOTAL(sum(paidamt))/Lag_Factors))/sum(TOTAL(FFS_membership))

Note: The expression "sum(paidamt)/Lag_Factors" is the expression that creates the Proj Incurred Claims field.

The screenshot below is from my excel spreadsheet that depicts the expression I am trying to replicate in qlikview.

Please excuse my terrible screenshots. I can only show enough to indicate what I am trying to do.

Does anyone have any suggestions on how to do this?

Thank you,

Re: Expression based on row cells in chart

Vegar — Sat, 03 Jun 2023 06:30:25 GMT

You can probably solve this by using set analysis. I did not quite understand your logic of selecting periods. Below an example where I calculate for all years greater or equal to last year.

If you need another logic then please explain and also let us know which calendar fields that you have available. Is it only Year and Month or do you have more fields?

SUM({< [Incurred Year]={">=$(=year(today())-1)"}>} [Proj Incurred Claims])

/ SUM({< [Incurred Year]={">=$(=year(today())-1)"}>} [FFS_membership])

topic Expression based on row cells in chart in QlikView

Expression based on row cells in chart

Re: Expression based on row cells in chart