Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

deepaksingh
New Contributor

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

Re: Using expression in other expression to calculate

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.


deepaksingh
New Contributor

Re: Using expression in other expression to calculate

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
Honored Contributor

Re: Using expression in other expression to calculate

May be this:

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

deepaksingh
New Contributor

Re: Using expression in other expression to calculate

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.

MVP
MVP

Re: Using expression in other expression to calculate

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
Honored Contributor

Re: Using expression in other expression to calculate

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?

MVP
MVP

Re: Using expression in other expression to calculate

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?

deepaksingh
New Contributor

Re: Using expression in other expression to calculate

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'}

MVP
MVP

Re: Using expression in other expression to calculate

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

Community Browser