topic Re: adding calculated vales in dimension in QlikView

adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 09:41:53 GMT

Dear Community,

I have tricky problem like below....I dont know how i can do with this one

I have table like below

Remarks Sum of Salary

AFaboulous 212000

BPerformance 104000

CExcellent 57000
DVery Good 68000

EGood 87000

FAverage 35000

GPoor 69000

Now, my requirement is i have to add one calculated value in Remarks dimension like below

Remarks Sum of Salary

AFaboulous 212000

BPerformance 104000

CExcellent 57000

Conversion 27%
DVery Good 68000

EGood 87000

FAverage 35000

GPoor 69000

Formula for conversion is = CExcellent (57000) / AFaboulous (212000) * 100 = 27%

I tried in my attached QVW file but did not get as expected..........Kindly help me with this ...

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 09:48:48 GMT

Hi Sarfaraz, you can use this expression:

If(Remarks<>'Conversion',

Num(Sum(SALARY), '0'),

Num(Sum(TOTAL {<Remarks={'CExcellent'}>} SALARY)/Sum(TOTAL {<Remarks={'AFaboulous'}>} SALARY), '0%')

)

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 09:53:48 GMT

Dear Ruben,

Where i will have to use this expression .....In Calculated dimension or in Expression ..

I have already code in Expression like below

Sum(SALARY)

Kindly suggest ...

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 09:56:46 GMT

Hi Sarfaraz, that should be your expression, replace Sum(SALARY) to that expression.

That calculates the Sum(SALARY) with integer format for any Remark except for 'Conversion' wich will have the expression to calculate the percentage as an integer %.

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 10:20:33 GMT

Dear Ruben,

Thanks for your solution it is working as expected ....However i want to implement the same in my project App.

In my project the chart look like below.....

look at in chart the conversion field is added ......similarly here in this chart the conversion is get calculated by

conversion = ISales_Closed_Final / ATotal_Leads_Considered * 100

I have implemented your said logic in my app ...however not getting conversion field there ...

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 10:47:33 GMT

Hi Sarfaraz, I didn't have the 'Conversion' status so I used totals column, also some values are slightly different, check attachment maybe this gives you a hint to solve your problem.

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 10:58:44 GMT

Dear Ruben,

Conversion field was not there .....as we have to add that field which will show the percentage of between two columns..

Is it possible to add column as Conversion as per above format...

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 11:10:54 GMT

Hi Sarfaraz, you can change the format of the total column to feel like an another column, but if using a total doesn't works for you maybe you can add Conversion as status_description and check the status_description value in an if to use the regular expression or the Conversion expression.

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 11:16:26 GMT

Dear Ruben,

How i can add the conversion as Status description .......

Can you keep any example here for my understanding so that it would be much appreciable .......

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 11:21:56 GMT

Hi Sarfaraz, your qvw script starts with an inline table, there you can add the values to create the Conversion status, ie:

[STATUS_ID, Status_Description,Id

, Conversion, 14

80, BInsurer_reject, 2

....

Then probably you'll need to adjust the expression, hard to say the needed expression without the possibility to test.

Re: adding calculated vales in dimension

anlonghi2 — Mon, 20 Apr 2015 11:25:41 GMT

Hi Sarfaraz,

try with these:

if(Remarks='Conversion',num(sum(all if(Remarks='CExcellent',SALARY))/sum(all if(Remarks='AFaboulous',SALARY)),'##0 %'),Sum(SALARY))

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 11:42:38 GMT

Dear Ruben,

I have added the 'Conversion' field in status description ..........I also adjusted the expression accordingly but nothing is appear in that column ...

Below is my expression

If(wildmatch(Status_Description,'JConversion'),

Num(Sum(Aggr(Sum(Distinct{<SELF_FLAG={1},BUS_TYPE_ID-={9}, Status_Description={'ISales_Closed_Final'}>}PREMIUM_WITHOUT_ST),LEAD_NO))

/Sum(Aggr(Sum(Distinct{<SELF_FLAG={1},BUS_TYPE_ID-={9}, Status_Description={'ATotal_Leads_Considered'}>}PREMIUM_WITHOUT_ST),LEAD_NO))*100, '0.00'),

Num(Sum(Aggr(Sum(Distinct{<SELF_FLAG={1},BUS_TYPE_ID-={9} >}PREMIUM_WITHOUT_ST),Status_Description,LEAD_NO)), '0')

)

My qvw is also attached here for your refernce ....

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 12:35:30 GMT

Hi, ok, it's not getting a related value for the ERGO_LOC, I think the easier way to solve that is modifying the inline table adding different rows to JConversion, giving the different IDs of the statusdescription that has to use (Isales_Closed_Final and ATotal_Leads_Considered), ie:

87, JConversion, 14

92, JConversion, 14

94, JConversion, 14

....

This way JConversion will be related to the values need to calculate the expression, but it's hard to say without the possibility of doing test reload.

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 13:00:10 GMT

Dear Ruben,

As i have done what you told to do me .....I got the expected answer as well but it works on selection basis ..

Look at snapshot below ....

Once i click on conversion it will calculation value appears but when i unselect the conversion ....Jconversion column getting blank ....

What to do i am confused ......kindly suggest

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 13:02:41 GMT

Please upload that document to test.

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 13:14:13 GMT

Please find the attached QVW here ...

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 13:25:29 GMT

Hi Sarfaraz, you'll need to use total to 'exit' from current Status, ie:

if(Status_Description <>'JConversion',

Num(Sum(Aggr(Sum(Distinct PREMIUM_WITHOUT_ST),Status_Description,LEAD_NO)), '0'),

Num(Sum(TOTAL <ERGO_LOC> Aggr(Sum(Distinct{<Status_Description={'ISales_Closed_Final'}>}PREMIUM_WITHOUT_ST),LEAD_NO))

/Sum(TOTAL <ERGO_LOC> Aggr(Sum(Distinct{<Status_Description={'ATotal_Leads_Considered'}>}PREMIUM_WITHOUT_ST),LEAD_NO))*100, '0.00')

)

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 13:49:58 GMT

Dear Ruben,

Brilliant work ....Now it is working fine as expected.........However One more query to ask you

I want to suppress the null value in that particular chart .........

look at in above chart........Ergo_Location contains null value I want to suppress it ........but want to keep the 0 values column like ....BInsurer_reject,ELead_Pending_w/u_underwriter , F_Noncontactable etc.

when i checked the supress when value is null on Ergo_Loc then it will hide the 0 values column also .....But want to keep o valuese column ....and not to keep null values of Ergo_Loc

Please help ....

Sarfaraz

Re: adding calculated vales in dimension

rubenmarin — Mon, 20 Apr 2015 14:13:35 GMT

Hi Sarfaraz, that one I don't know how to solve, maybe works a workaround using calculated dimension like:

=Aggr(If(IsNull(ERGO_LOC), 'No Location', ERGO_LOC), ERGO_LOC)

Re: adding calculated vales in dimension

sarfaraz_sheikh — Mon, 20 Apr 2015 14:22:24 GMT

Dear Ruben,

See, if you can find any concrete logic for the same........I also tried at my end did not achived the same.

Request you to suggest me something regards the same !!!!!!

Sarfaraz