13 Replies Latest reply: Jul 31, 2018 8:46 AM by Sunny Talwar

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

• ###### 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.

• ###### 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= ?

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

• ###### 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.

• ###### 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?

• ###### 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.

• ###### 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?

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

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

• ###### Re: Using expression in other expression to calculate

Hi Sunny,

I am using this in pivot table in QlikSense for hierarchy thats why I told you for multiple dimension, but in logic no need to mention dimension, I tried below without dimension but its showing null records. Please guide

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,

CUST_CD&DIST_CD)))))

• ###### Re: Using expression in other expression to calculate

I tried below without dimension but its showing null records.

Why would you do that? Are you not sure at what dimension level you want to evaluate this at? Let's look at an example may be that will help

Country, Car, Sales

US, Civic, 10

US, Accord, 500

Japan, Civic, 500

Japan, Accord, 100

Now I can see that I want Sum(Sales) to be greater than 160 and less than 590... but at what level? for Country? For Car or for Country and Car

For Country, my output will be

Country, Car, Sales

US, Civic, 10

US, Accord, 500

Because for Country my sales are 510 and 600 and Japan doesn't meet the condition

For Car, my output will be

Country, Car, Sales

US, Civic, 10

Japan, Civic, 500

Because for Car, my sales for civic is 510 and for Accord is 600.

For Both Car and Country

Country, Car, Sales

US, Accord, 500

Japan, Civic, 500

I think you get my point....

• ###### Re: Using expression in other expression to calculate

Yeah,

I am counting customer for HOS dimension(Delhi, Mumbai,chennai) where KEYACCGRP2_CD = {'VISI'} and MTD_VOL range will be >0<40

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

• ###### Re: Using expression in other expression to calculate

Try this

Count({<KEYACCGRP2_CD = {'VISI'}, HOS = {"=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)