Using expression in other expression to calculate

[DS14]

I Have Calculated MTD_VOL using this expression:

sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL)

and now i have to use above MTD_VOL expression in another expression for range ">0<40" i.e MTD_VOL={">0<40"} in below expression:

count ( {<(MTD_VOL)={">0<40"},KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

How i can Achieve Please help

[petter]

You are not allowed to do nested aggregations directly in Qlik. However you can use either the Aggr() function or an "Advanced Search" to achieve it.

Count ( {<UniqueDIM={

"=

Sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL)

>0 AND

Sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL)

<40"

},KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

You could put the repeated Sum-calculation in a variable as a string called vSumPVOL and make the expression simpler like this:

vSumPVOL:

Sum({<INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>} PACK_VOL)

Then the count expression can use this variable like this:

Count {<UniqueDIM={"=$(=vSumPVOL)>0 AND $(=vSumPVOL)<40)"}>} , KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

The key for this to work is that the UniqueDIM is a correctly chosen dimension for the advanced search to be matched with. To be absolutely sure that it performs the search a unique dimension could be used.

[DS14]

Hi Skjolden,

MTD_VOL   is not a field its calculated using some logic, then how you are defining it in the expression as

MTD_VOL= ?

[balabhaskarqlik]

May be this:

Count {<MTD_VOL={"=$(=MTD_VOL)>0 AND $(=MTD_VOL)<40)"}>} , KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

[DS14]

MTD_VOL =  sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL)

above expression I have to use in place of MTD_VOL within given range

count ( {<(MTD_VOL)={">0<40"},KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)

(MTD_VOL) doesn't exist in database as a field, its calculated using some expression, how can you take MTD_VOL = in expression.

[petter]

You are right - and I have corrected my answer now above with the use of UniqueDIM as a field ... You will have to find a unique field int the datamodel or create one in your data model - which is easy by using RowNo() or RecNo() functions in the load statements.

[balabhaskarqlik]

In your Count() expression, You want to consider : (MTD_VOL)={">0<40"} and you want to consider some fields values = to MTD_VOL - 0 to 40. what's the field name you want to take MTD_VOL values range?

[sunny_talwar]

Is there a single or multiple dimension/s against which you are trying to evaluate this condition?

sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL) > 0

and

sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL) < 40?

[DS14]

multiple dimension

i have to count customer for which MTD_Vol is >0<40.

and MTD_VOL logic is =

sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DD-MM-YYYY'))<=$(=Date(Max(SO_DT),'DD-MM-YYYY'))"}>}

PACK_VOL)

so now the actual logic will be

count(customer) for whom MTD_VOL range will be >0<40 and KEYACCGRP2_CD={'VISI'}

[sunny_talwar]

May be you use need to use Aggr() in that case

Count({<KEYACCGRP2_CD = {'VISI'}>} Aggr(If(

Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT), 'DD-MM-YYYY'))"}>} PACK_VOL) > 0

and

Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DD-MM-YYYY'))<=$(=Date(Max(SO_DT), 'DD-MM-YYYY'))"}>} PACK_VOL) < 40,

Customer), ListOfDimensions))