topic Re: Dimension Related in QlikView

Dimension Related

sarfaraz_sheikh — Tue, 17 Mar 2015 08:27:09 GMT

Dear ALL,

Below is my snapshot in which i am using simple Wildmatch function.........

below is calculated dimension code..

=if(Wildmatch(STATUS_ID,80)>0,'Insurer_reject',
        if(Wildmatch(STATUS_ID,75)>0,'Follow_Up',
         if(wildmatch(STATUS_ID,77)>0,'Lead Lost',
             if(wildmatch(STATUS_ID,82)>0,'Lead Pending U/W',
                     if(wildmatch(STATUS_ID,79)>0,'Non Contactable',
                         if(wildmatch(STATUS_ID,78)>0,'Not Interested',
                            if(wildmatch(STATUS_ID,99)>0,'Pending for PPC',
                                 if(wildmatch(STATUS_ID,74)>0,'Sales Open Default',
                                    if(wildmatch(STATUS_ID,87,92,94,97,91,93,90,103,121,123)>0,'Sales closed final',

                                     if( wildmatch(STATUS_ID,'74','75','77','78','79','80','82','87','89','92','94','97','91','93','90','99','103','114','121','123')>0,'Total Leads Login',
                                      ))))))))))

i am not getting total leads login as dimension content.....kindly help if any tricks...

My QVW is also attached here for your reference.........

Sarfaraz

Re: Dimension Related

Gysbert_Wassenaar — Wed, 18 Mar 2015 14:36:31 GMT

An expression can only return one value, not two. So STATUS_ID 80 is either 'Insurer_reject' or 'Total Leads Login'. It cannot be both values at the same time.

Re: Dimension Related

hic — Wed, 18 Mar 2015 14:42:50 GMT

I suggest you do this coding in the script instead. E.g.

Load * inline

[STATUS_ID, Status

80, Insurer_reject

75, Follow_Up

...

];

HIC

Re: Dimension Related

Gysbert_Wassenaar — Wed, 18 Mar 2015 14:49:30 GMT

And to expand on what Henric posted. Create an extra record for each status id that should also be shown as Total Leads Login:

Load * inline

[STATUS_ID, Status

80, Insurer_reject

75, Follow_Up

...

80, Total Leads Login

75, Total Leads Login

...

];

Re: Dimension Related

Roop — Wed, 18 Mar 2015 16:28:37 GMT

I think that I understand what you require. The sum of all of the various STATUS_ID descriptors is "Total Leads Login". The table below shows the complete picture.

So how do we get there?

Your dimension structure worked well although I think there are other better ways round it as Henric and Gysbert have already stated above.

I changed your dimension structure slightly to:

=if(Wildmatch(STATUS_ID,80)>0,

'Insurer_reject',

if(Wildmatch(STATUS_ID,75)>0,

'Follow_Up',

if(wildmatch(STATUS_ID,77)>0,

'Lead Lost',

if(wildmatch(STATUS_ID,82)>0,

'Lead Pending U/W',

if(wildmatch(STATUS_ID,79)>0,

'Non Contactable',

if(wildmatch(STATUS_ID,78)>0,

'Not Interested',

if(wildmatch(STATUS_ID,99)>0,

'Pending for PPC',

if(wildmatch(STATUS_ID,74)>0,

'Sales Open Default',

if(wildmatch(STATUS_ID,87,92,94,97,91,93,90,103,121,123)>0,

'Sales closed final',

Null()

) ) ) ) ) ) ) ) )

Notice the "Null()" as the final else in the above IF statements.

In the Pivot table under the Dimensions tab, select the above dimension and check the box "Suppress When Value is Null" and this will omit any other value of STATUS_ID from the total.

In the Pivot table under the Presentation tab, select the above dimension and check the box Show Partial Sums and enter the text "Total Leads Login" in the Label for Totals entry box

I hope that this helps