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

[DS14]

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

[sunny_talwar]

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

[DS14]

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)

[sunny_talwar]

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)