Funnel Chart Idea

[#1]

Hi,

I want to create a funnel chart as follows...

If in a sheet of data, the occurance of 'A' is less than 5, It categorizes him in LOW group. If Occurance of A > 5, it categorizes him in HIGH group.

I have a set of data having more than 200 rows and 15 distinct values in it. I want in that way.

I tried applying few logics in mind but I didn't find a way. I know a bit about set analysis but we can't directly apply search criteria on constant ( not a right logic )

Logic what I think is -

1 - Filter out distinct value from the set of values

2 - Store those values in a single variable (or array / or container / or SQL IN alike thing)

3 - Apply that variable in the final set analysis logic to just get the distinct values.

4 - COUNT the total number of values we got for a particular distinct value.

5 - Apply some sort of If...else command to satisfy the need.

This lenghty logic may solve my requirement but I m not able to figure out how to write those in QlikView. Or maybe there could be an easier or convenient way of doing the same.

Please enlighten me on this case.

If you guys need sample data, I can give it to you.

[kaushiknsolanki]

HI sachin,

              All you have to do is change the if statement in script as follows

Group:

load

if(count(B_Num) = 0,'None',

if(count(B_Num) > 0 and count(B_Num) <= 5 ,'Low',

if(count(B_Num) > 5 and count(B_Num) <= 10 ,'Medium',

if(count(B_Num) > 10 and count(B_Num) <= 15 ,'High','Very High'

)))) as Grp,

A_Num

Resident DATA Group by A_Num; 

           Try and Let me know

Thanks

[#3]

Hi.

It throwing error as I m not doing inline table load.

Can you demonstrate with my data if possible ?

[kaushiknsolanki]

Hi Sachin,

             I am sending the File Please have a look.

             If it don't work then send me your data.

Thanks

[#5]

Please check the QVW and XLS file.

XLS Here - http://community.qlik.com/servlet/JiveServlet/download/116906-14291/FunnelSampleCDR.qvw

Everywhere in Funnel Chart, count is 5.

Thanks.

[kaushiknsolanki]

HI,

    The XLS file is not visible can you please upload it again

Thanks

[#7]

Here is the XLS

[kaushiknsolanki · Accepted solution]

Hi

     Try following

//-------------Script------------//

Data:
LOAD [A Number],
     [B Number],
     Charge,
     [Call Duration]
    
FROM

(ooxml, embedded labels, table is Sheet1);

Group:
load
if(count([B Number]) = 0,'None',
if(count([B Number]) > 0 and count([B Number]) <= 5 ,'Low',
if(count([B Number]) > 5 and count([B Number]) <= 10 ,'Medium',
if(count([B Number]) > 10 and count([B Number]) <= 15 ,'High','Very High'
)))) as Grp,
[A Number]
Resident Data group by [A Number];

Thanks

[kaushiknsolanki]

Hi sachin,

              I am also uploading the document.

Thanks

[#10]

Yes, I rectified it exactly prior to this post but funnel is not showing correctly. Everywhere just 5 count whenever we do MouseHover on Funnel chart.

[kaushiknsolanki]

Hi sachin,

               Funnel shows 5 cause its from data only....

               Change data some what and then check.,

Thanks