adding calculated vales in dimension

[sarfaraz_sheikh]

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

[rubenmarin]

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

)

[sarfaraz_sheikh]

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

[rubenmarin]

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

[sarfaraz_sheikh]

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

[rubenmarin]

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.

[sarfaraz_sheikh]

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

[rubenmarin]

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.

[sarfaraz_sheikh]

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

[rubenmarin]

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.