topic Variable value 0 after applying aggr its getting null in calcualted dim but in expression working . in QlikView

Variable value 0 after applying aggr its getting null in calcualted dim but in expression working .

deepak_gupta — Wed, 25 Nov 2020 16:16:04 GMT

Hi Expert,

i have created 2 different variable,

when apply claim wise aggr function it's getting null in calculated dimension but in expression value give.

for example if i am having claim wise 2 differnet figures of above two variable, but there is in first variable value is zero

and second variable is having some value so after applying aggr function over there claim wise. it's give me null values in calculated dimension but in expression this is working.

1st expression which is having 0 value -

aggr(sum({<[Link Flag]={'PAID'},[Claims Flag]={'TP'},[Link Date]={'<=$(vEndDate)>=$(vStartDateFY)'},[Link Month],[Link Quarter],[Link Year]>}[Gross Claim Paid Amount]),[Claim Number])

2nd expression which is having some values -

aggr(sum({<[No Claim Status]={'Repudiation','Closure','Withdrawn'},[Link Flag]={'NC'},[Claims Flag]={'TP'},[Link Date]={'<=$(vEndDate)>=$(vStartDateFY)'}>}[No Claim Expense Amount]),[Claim Number])

so when i add both expression (expr 1) + (expr 2) and show in calculated dimension its show null.

Kindly help thanx in advance.

Re: Variable value 0 after applying aggr its getting null in calcualted dim but in expression working .

olimonbi29 — Sun, 04 Feb 2018 21:53:00 GMT

Try this apparently a coma is missing:

aggr(sum({<[Link Flag]={'PAID'},[Claims Flag]={'TP'},[Link Date]={'<=$(vEndDate)>=$(vStartDateFY)'},[Link Month],[Link Quarter],[Link Year]>},[Gross Claim Paid Amount]),[Claim Number])

2nd expression which is having some values -

aggr(sum({<[No Claim Status]={'Repudiation','Closure','Withdrawn'},[Link Flag]={'NC'},[Claims Flag]={'TP'},[Link Date]={'<=$(vEndDate)>=$(vStartDateFY)'}>},[No Claim Expense Amount]),[Claim Number])

Re: Variable value 0 after applying aggr its getting null in calcualted dim but in expression working .

vishsaggi — Sun, 04 Feb 2018 22:25:06 GMT

May be try this?

= Sum( aggr(sum({<[Link Flag]={'PAID'},[Claims Flag]={'TP'},[Link Date]={"<=$(vEndDate)>=$(vStartDateFY)"},[Link Month],[Link Quarter],[Link Year]>}[Gross Claim Paid Amount]),[Claim Number]))

= Sum( aggr(sum({<[No Claim Status]={'Repudiation','Closure','Withdrawn'},[Link Flag]={'NC'},[Claims Flag]={'TP'},

[Link Date]={"<=$(vEndDate)>=$(vStartDateFY)"}>}[No Claim Expense Amount]),[Claim Number]))

Then use these expression to add. Use like RangeSum()

= RangeSum(FirstExprLabel, SecondExprLabel)

Re: Variable value 0 after applying aggr its getting null in calcualted dim but in expression working .

deepak_gupta — Mon, 05 Feb 2018 05:31:58 GMT

Thanks for your response Nagaraju,

same problem your approach is working in Expression but not working in Calculated Dimension.

when applying rangemax in dimension it's show Error (error in calculated Dimension).

Re: Variable value 0 after applying aggr its getting null in calcualted dim but in expression working .

vishsaggi — Mon, 05 Feb 2018 17:49:57 GMT

‌if you want to use in calculated dimensions you have to use aggr() around the expression...

like = aggr(your expression, fieldname)