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

[anlonghi2]

Hi Sarfaraz,

try with these:

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

[sarfaraz_sheikh]

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

[rubenmarin]

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.

[sarfaraz_sheikh]

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

[rubenmarin]

Please upload that document to test.

[sarfaraz_sheikh]

Please find the attached QVW here ...

Sarfaraz

[rubenmarin]

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

)

[sarfaraz_sheikh]

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

[rubenmarin]

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)

[sarfaraz_sheikh]

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