
Re: Using expression in other expression to calculate
Petter Skjolden Jul 31, 2018 5:18 AM (in response to Deepak Singh )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)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
PACK_VOL)
>0 AND
Sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
PACK_VOL)
<40"
},KEYACCGRP2_CD=={'VISI'} >} CUST_CD&DIST_CD)
You could put the repeated Sumcalculation 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)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>} 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
Deepak Singh Jul 31, 2018 5:23 AM (in response to Petter Skjolden )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
Bala Bhaskar Jul 31, 2018 6:39 AM (in response to Deepak Singh )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
Deepak Singh Jul 31, 2018 6:52 AM (in response to Bala Bhaskar)MTD_VOL = sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
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
Bala Bhaskar Jul 31, 2018 7:13 AM (in response to Deepak Singh )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
Petter Skjolden Jul 31, 2018 7:12 AM (in response to Deepak Singh )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
Sunny Talwar Jul 31, 2018 7:25 AM (in response to Deepak Singh )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)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
PACK_VOL) > 0
and
sum({< INV_STATUS={'S'},OPERATION={'SAH'},SO_DT={">=$(=Date(Monthstart(Max(SO_DT)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
PACK_VOL) < 40?

Re: Using expression in other expression to calculate
Deepak Singh Jul 31, 2018 8:04 AM (in response to Sunny Talwar )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)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
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
Sunny Talwar Jul 31, 2018 8:07 AM (in response to Deepak Singh )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)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT), 'DDMMYYYY'))"}>} PACK_VOL) > 0
and
Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT), 'DDMMYYYY'))"}>} PACK_VOL) < 40,
Customer), ListOfDimensions))

Re: Using expression in other expression to calculate
Deepak Singh Jul 31, 2018 8:26 AM (in response to Sunny Talwar )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)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT),
'DDMMYYYY'))"}>} PACK_VOL) > 0
and
Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT),
'DDMMYYYY'))"}>} PACK_VOL) < 40,
CUST_CD&DIST_CD)))))

Re: Using expression in other expression to calculate
Sunny Talwar Jul 31, 2018 8:35 AM (in response to Deepak Singh )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
Deepak Singh Jul 31, 2018 8:44 AM (in response to Sunny Talwar )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)),'DDMMYYYY'))<=$(=Date(Max(SO_DT),'DDMMYYYY'))"}>}
PACK_VOL)

Re: Using expression in other expression to calculate
Sunny Talwar Jul 31, 2018 8:46 AM (in response to Deepak Singh )Try this
Count({<KEYACCGRP2_CD = {'VISI'}, HOS = {"=Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT), 'DDMMYYYY'))"}>} PACK_VOL) > 0 and Sum({< INV_STATUS = {'S'}, OPERATION = {'SAH'}, SO_DT = {">=$(=Date(MonthStart(Max(SO_DT)), 'DDMMYYYY'))<=$(=Date(Max(SO_DT), 'DDMMYYYY'))"}>} PACK_VOL) < 40"}>} Customer)





