topic About Problem in QlikView

About Problem

sarfaraz_sheikh — Mon, 13 Apr 2015 07:16:03 GMT

Dear All,

I have one user requirement like below,

As of now the chart is looking like below :-

However I want my chart to appear like below..one more conversion field i want immijiate next to ISales_Closed_Final.

Calculation for Conversion ........

ATotal_Leads_Considered ISales_Closed_Final Conversion

Amount=197457 Amount=177247 177247 / 197457 = 89.76

Count=28 Count=25 25/28 *100 =89.28

Is this possible in above pivot chart ....Because all the columns in Blue colour are the dimensions content ...at dimension level how i can calculate percentage between two fields ...

Is there any way if yes the solution would be much appreciable .... There is requirement like this from user...

Sarfaraz

Re: About Problem

Mon, 13 Apr 2015 07:43:43 GMT

Hi,

Try like below,

num(

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

Sum(Aggr(Sum({<Status_Description={'ATotal_Leads_Considered'},ERGO_LOC={'CHENNAI'}>}Distinct PREMIUM_WITHOUT_ST),Status_Description,LEAD_NO))),'#,##0%')

Re: About Problem

sarfaraz_sheikh — Mon, 13 Apr 2015 08:01:29 GMT

Dear Harshal,

Thanks for your reply ....I know the calculation formula but my concern is how i can show these in pivot chart like..

Similar like below :---

look at snapshot ...there is one field Convesion added immijiate next ISales_Closed_Final ......I want format like above chart...is this possible ???

Sarfaraz

Re: About Problem

Mon, 13 Apr 2015 09:09:38 GMT

Hi,

As per your requirement may be it is not possible in pivot But try like below approach,

Create Flag like below in script level,

Flag_LMS_Testing:

Load *,

if(wildmatch(Status_Description,'ATotal_Leads_Considered'),1,if(wildmatch(Status_Description,'ISales_Closed_Final'),0)) as Flag

resident LMS_Testing;

drop table LMS_Testing;

New_LMS_Testing:

Load *,

Sum({<Flag=0>}Distinct PREMIUM_WITHOUT_ST)/Sum({<Flag=1>}Distinct PREMIUM_WITHOUT_ST) as conversion

resident Flag_LMS_Testing group by Status_Description,LEAD_NO;

drop table Flag_LMS_Testing;

Then take table with Erco_Log,conversion fields...

Re: About Problem

sarfaraz_sheikh — Mon, 13 Apr 2015 09:48:34 GMT

Dear Patil,

Thanks for your solution........But it did not fullfill my requirement....Understand that might be it has not possible in Qlikview ..

But again it is my request from community to see if any concrete logic at Dimension content level would be more helpful...

Sarfaraz