Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

Using expression in other expression to calculate

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

13 Replies
petter
Partner - Champion III
Partner - Champion III

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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
Partner - Contributor III
Partner - Contributor III
Author

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))