topic Re: need help to understand a expression ? in QlikView

need help to understand a expression ?

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

Hi All ,

Please assist me in explaining the below expression :

1. vCmhTotalIncome =

sum({

<DOC_Deal_Data.DOCCategory={'DIC Income'}>+

<DOC_Deal_Data.DOCCategory={'First Gross Income'},DOC_Deal_Data.ProductName={'FirstGross'}>+

<DOC_Deal_Data.DOCCategory={'Extra Income'},DOC_Deal_Data.ProductName={'Admin Fee'}>+

<DOC_Deal_Data.DOCCategory={'Vap Income'},DOC_Deal_Data.ProductName={'Body Insurance','Credit Life','Credit Shortfall','Deposit Cover','Maintenance/Service Plan','Road Cover Income','Paint/Glass Protection','Tyre/Rim Warranty','Warranty','Tracking Income','Inflation Protection','Other Income','Comp Insurance'}>

}

DOC_Deal_Data.CommAmount)

2. vCmhTotalIncomeAggr = Aggr(NODISTINCT $(vCmhTotalIncome),Dealer.id)

The variable (vCmhTotalIncome) is showing value , But the variable (vCmhTotalIncomeAggr) is coming null . I need to find out why its coming null.

Thanks,

Re: need help to understand a expression ?

Anil_Babu_Samineni — Thu, 21 Dec 2017 09:08:57 GMT

Can you try Variable in text box and show the image?

Re: need help to understand a expression ?

suvechha_b — Thu, 21 Dec 2017 09:22:26 GMT

1. vCmhTotalIncome =

sum({

<DOC_Deal_Data.DOCCategory={'DIC Income'}>+

<DOC_Deal_Data.DOCCategory={'First Gross Income'},DOC_Deal_Data.ProductName={'FirstGross'}>+

<DOC_Deal_Data.DOCCategory={'Extra Income'},DOC_Deal_Data.ProductName={'Admin Fee'}>+

}

DOC_Deal_Data.CommAmount)

The variable (vCmhTotalIncome) no. 1 in text box is showing 96660345 while in the table the total is 323843

2 vCmhTotalIncomeAggr is coming null.

Thanks,

Re: need help to understand a expression ?

Anil_Babu_Samineni — Thu, 21 Dec 2017 09:28:06 GMT

Try this for a while

Aggr(NODISTINCT sum({<DOC_Deal_Data.DOCCategory={'DIC Income'}>+

<DOC_Deal_Data.DOCCategory={'First Gross Income'},DOC_Deal_Data.ProductName={'FirstGross'}>+

<DOC_Deal_Data.DOCCategory={'Extra Income'},DOC_Deal_Data.ProductName={'Admin Fee'}>+

Note - Instead Dealer.id, It should be your dimensions only. And why want to use NoDistinct with aggr() ??

Re: need help to understand a expression ?

sunny_talwar — Thu, 21 Dec 2017 12:39:46 GMT

Are you suppressing null values for your dimension? If you are then, try to un-suppress them and check if the value matches or not...

If then you want the same number in your text box as your table, just add this to your set analysis

{<Branch = {"*"}>}

Re: need help to understand a expression ?

pradosh_thakur — Thu, 21 Dec 2017 13:14:21 GMT

Hi Suvechha

aggr() creates a temporary table for the dimension you provide inside it. so if you aggr() returns more than one value for a straight table dimension it will not show up unless you use some agggragation function like sum or max .so my suggestion would be try sum(Aggr(NODISTINCT $(vCmhTotalIncome),Dealer.id)) or sum(Aggr(NODISTINCT $(=vCmhTotalIncome),Dealer.id)) in your table as well as text box and see what result you are getting.

regards

Pradosh