topic Re: Expr in QlikView

Expr

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a field called SPEND which has two strings in it Finance and Sales Iam using below expression in the PIE chart which dont have any dimensions ..this will display me the percentage value .. Can someone please tell me how do i include the DImensions value for only FInance in this expression ? this expression should always display the value for Finance from SPEND field .....

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

Thanks in advance

Re: Expr

MK_QSL — Wed, 12 Jul 2017 08:08:38 GMT

Add below as calculated dimension

IF(SPEND = 'Finance',SPEND)

Tick Suppress when value is null

Re: Expr

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:08:52 GMT

May be this?

num( ( sum(aggr( if( round( ( (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal))

(Sum({}CurrQtyReceived * PriorCostLocal)) ) - ( (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced))) ) ) >0,

(Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal)) ,0) ,Entity_Item_Curr_MonthID,SpendCol)) ) / (Sum({<Spend = {Finance}>}CurrQtyReceived * CurrCostLocal)) ,'#,##0.00%')

Re: Expr

smilingjohn — Wed, 12 Jul 2017 08:21:28 GMT

Hi Anil,

This is showing error

Re: Expr

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:29:21 GMT

Looks like i missed it here. If below on is not helping can you show the error image where we getting?

num( ( sum(aggr( if( round( ( (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal))

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * PriorCostLocal)) ) - ( (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced))) ) ) >0,

(Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal)) ,0) ,Entity_Item_Curr_MonthID,SpendCol)) ) / (Sum({<Spend = {'Finance'}>}CurrQtyReceived * CurrCostLocal)) ,'#,##0.00%')

Re: Expr

smilingjohn — Wed, 12 Jul 2017 08:29:45 GMT

Hi Anil THis is the real Expression ...in which i have to include SPEND for finance and show the values permanenrtly

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

Re: Expr

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:31:59 GMT

I would always check typo error first? Can you remove that?

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},>}CurrQtyReceived * CurrCostLocal))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * PriorCostLocal))

)

(

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

>0,

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

(Sum({<MCERFlag = {1}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

Re: Expr

Re: Expr

smilingjohn — Wed, 12 Jul 2017 08:38:38 GMT

I have removed it Anil ,

I added that comma only to add the (Sum({<MCERFlag = {1}>,SPEND={'Finance'} >}CurrQtyReceived * CurrCostLocal))

Re: Expr

smilingjohn — Wed, 12 Jul 2017 08:40:35 GMT

HI Manish

This willl not work i want to display the value permamnenetly for Finance withought any dimension or calculated dimesion .....

Re: Expr

Anil_Babu_Samineni — Wed, 12 Jul 2017 08:42:05 GMT

Again typo error

(Sum({<MCERFlag = {1}>,SPEND={'Finance'} >}CurrQtyReceived * CurrCostLocal))

Re: Expr

Kushal_Chawda — Wed, 12 Jul 2017 09:12:54 GMT

num(

(

sum(aggr(

if(

round(

(

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * CurrCostLocal))

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * PriorCostLocal))

)

(

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(CurrCostLocal/CurrCostInvoiced)))

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * CurrCostInvoiced*(PriorCostLocal/PriorCostInvoiced)))

)

>0,

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * CurrCostLocal))

,0)

,Entity_Item_Curr_MonthID,SpendCol))

)

(Sum({<MCERFlag = {1},Spend={'Finance'}>}CurrQtyReceived * CurrCostLocal))

,'#,##0.00%')

)

Re: Expr

smilingjohn — Wed, 12 Jul 2017 09:57:14 GMT

kushal this is not working

Re: Expr

Kushal_Chawda — Wed, 12 Jul 2017 09:58:44 GMT

Please share the sample file with expected output