topic Re: Chart/Graph Dimension Sort/Order on x Axis in QlikView

Chart/Graph Dimension Sort/Order on x Axis

Fri, 23 Mar 2012 13:33:21 GMT

HI All (my first post on QView community forum)

I am having problems with the sorting of the dimensions on the x axis of a chart.

This is the code for a dimension i have which groups a field i have.

The graph appears like this. With the ages completely out of order.

Chart/Graph Dimension Sort/Order on x Axis

SunilChauhan — Fri, 23 Mar 2012 13:54:36 GMT

sort-> expression wildmatch( expression, 'value1','value2',.....)_. ascending

wildmatch (if(deriver<=18,'<18'.......),'<18','19-23','24-28',...........)

hope this helps

Chart/Graph Dimension Sort/Order on x Axis

Fri, 23 Mar 2012 14:18:59 GMT

I couldn't get this to work. Could you type two full lines of the expression?

I think i'm getting a little confused.

Regards

James - MI GIB

Chart/Graph Dimension Sort/Order on x Axis

Fri, 23 Mar 2012 14:19:36 GMT

I couldn't get this to work. Could you type two full lines of the expression?

I think i'm getting a little confused.

Regards

James - MI GIB

Re: Chart/Graph Dimension Sort/Order on x Axis

jagan — Fri, 23 Mar 2012 14:41:56 GMT

Hi,

Check attached file for solution.

Hope it helps you.

Regards,

Jagan.

Chart/Graph Dimension Sort/Order on x Axis

johnw — Fri, 23 Mar 2012 23:11:25 GMT

In case it means anything to anyone, I do this sort of thing like Jagan suggests, by using dual() to assign a numeric value to the range.

Chart/Graph Dimension Sort/Order on x Axis

Mon, 26 Mar 2012 08:08:54 GMT

Apologies

i have ages 16 - 95. Would i need to create a Load inline for every age?

I also have around 50 other data items. would i need to create a preceeding load to bring in these additional items ??

Thanks for your help

James

Chart/Graph Dimension Sort/Order on x Axis

jagan — Mon, 26 Mar 2012 08:16:46 GMT

Hi,

Can you attach the sample file that you are working.

Regards,

Jagan.

Re: Chart/Graph Dimension Sort/Order on x Axis

Mon, 26 Mar 2012 08:20:58 GMT

This is my current code. Where would I put the LOAD * INLINE for the Driver_1_Age field ? ?

*****************************

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD

Insurer_Policy_Number,

year(Inception_Date) AS InceptionYear,

Inception_Date,

Duration_months,

Event_Code,

Scheme,

Transaction_Date,

Transaction_Net_Premium,

Transaction_Commission,

Transaction_Risk_Days,

Expiry_Date,

policy_id,

first_inception,

end_date,

product,

current_policy_counter,

Policy_Counter,

policy_number,

pnumber,

suffix2,

rnl,

nbs,

gwp,

netpluscomm,

netpluscommann,

contract,

Cover_Chunk_No,

Policy_Postcode,

Cover_Type,

Class_of_Use,

NCD_Years,

Other_NCD,

Intro_Discount,

Prot_Discount,

VDR_Code,

Number_of_Drivers,

Number_of_Convictions,

Number_of_Previous_Claims,

Contract_Type,

Main_Driver,

Number_of_Vehicles,

Source_Code,

Subscheme,

Agency,

Vhls_in_Household,

Transaction_IPT,

Transaction_Screen_Rate,

Transaction_Exposure_Days,

Transaction_Net_Earned_Premium,

Transaction_Number_of_Claims,

Transaction_Claim_Payments,

Transaction_Claim_Recoveries,

Transaction_Claim_Reserves,

Payment_Type,

Eff_From_Date,

Eff_From_Time,

Chunk_End_Date,

Status,

Driver_1_Age,

Driver_1_Sex,

Driver_1_Mar_Status,

Driver_1_Occ,

Driver_1_Licence,

Driver_1_Exp,

Driver_1_UK_Res,

Driver_1_Advanced_Qualifications,

Driver_1_Access_Other_Vhl,

Driver_1_Rel_Prop,

Vhl_1_Vehicle_Area_Code,

Vhl_1_Vol_Excess,

Vhl_1_Agreed_Value,

Vhl_1_ABI_Veh_Code,

Vhl_1_Veh_Group,

Vhl_1_Age,

Vhl_1_Value,

Vhl_1_Modified,

Vhl_1_Annual_Mileage,

Vhl_1_Garaged,

Vhl_1_Owner_Code,

Vhl_1_Keeper_Code,

Vhl_1_LH_Drive,

Vhl_1_Months_Owned,

Vhl_1_Body_Type,

Vhl_1_Number_of_Seats,

Vhl_1_Security_Device_1,

Vhl_1_Security_Device_2,

Vhl_1_Security_Device_3,

Vhl_1_Make,

Vhl_1_Model,

Vhl_1_registration_number,

Vhl_1_Overnight_postcode_1,

Vhl_1_Overnight_postcode_2,

Vhl_1_Engine_Capacity,

Driver_2_Age,

Driver_2_Sex,

Driver_3_Age,

Driver_3_Sex,

Driver_4_Age,

Driver_4_Sex,

ao_youngest_driver,

so_youngest_driver,

database,

Total_Payments,

Total_Recoveries,

Total_Estimates,

Total_Anticipated_Recoveries,

Total_Claim_Count,

incurred,

XOL_recoveries,

XOL_recoveries_nonNU,

number_of_capped_claims,

capped_costs,

zero_flag,

claim_count,

currexp,

net_earned_premium,

gep,

exposure,

retro,

pcode_formatted,

d_sector,

district,

Area_code,

Vehicle,

policy_suffix,

Source_System,

DID,

Area,

Payment_Made

FROM

Re: Chart/Graph Dimension Sort/Order on x Axis

Mon, 26 Mar 2012 08:25:07 GMT

This is my current code. Where would I put the LOAD * INLINE for the Driver_1_Age field ? ?

*****************************

SET ThousandSep=',';SET DecimalSep='.';SET MoneyThousandSep=',';SET MoneyDecimalSep='.';SET MoneyFormat='£#,##0.00;-£#,##0.00';SET TimeFormat='hh:mm:ss';SET DateFormat='DD/MM/YYYY';SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
LOAD
       Insurer_Policy_Number,
        year(Inception_Date)                                                AS InceptionYear,
     Inception_Date,
     Duration_months,
     Event_Code,
     Scheme,
     Transaction_Date,
     Transaction_Net_Premium,
     Transaction_Commission,
     Transaction_Risk_Days,
     Expiry_Date,
     policy_id,
     first_inception,
     end_date,
     product,
     current_policy_counter,
     Policy_Counter,
     policy_number,
     pnumber,
     suffix2,
     rnl,
     nbs,
     gwp,
     netpluscomm,
     netpluscommann,
     contract,
     Cover_Chunk_No,
     Policy_Postcode,
     Cover_Type,
     Class_of_Use,
     NCD_Years,
     Other_NCD,
     Intro_Discount,
     Prot_Discount,
     VDR_Code,
     Number_of_Drivers,
     Number_of_Convictions,
     Number_of_Previous_Claims,
     Contract_Type,
     Main_Driver,
     Number_of_Vehicles,
     Source_Code,
     Subscheme,
     Agency,
     Vhls_in_Household,
     Transaction_IPT,
     Transaction_Screen_Rate,
     Transaction_Exposure_Days,
     Transaction_Net_Earned_Premium,
     Transaction_Number_of_Claims,
     Transaction_Claim_Payments,
     Transaction_Claim_Recoveries,
     Transaction_Claim_Reserves,
     Payment_Type,
     Eff_From_Date,
     Eff_From_Time,
     Chunk_End_Date,
     Status,
     Driver_1_Age,
     Driver_1_Sex,
     Driver_1_Mar_Status,
     Driver_1_Occ,
     Driver_1_Licence,
     Driver_1_Exp,
     Driver_1_UK_Res,
     Driver_1_Advanced_Qualifications,
     Driver_1_Access_Other_Vhl,
     Driver_1_Rel_Prop,
     Vhl_1_Vehicle_Area_Code,
     Vhl_1_Vol_Excess,
     Vhl_1_Agreed_Value,
     Vhl_1_ABI_Veh_Code,
     Vhl_1_Veh_Group,
     Vhl_1_Age,
     Vhl_1_Value,
     Vhl_1_Modified,
     Vhl_1_Annual_Mileage,
     Vhl_1_Garaged,
     Vhl_1_Owner_Code,
     Vhl_1_Keeper_Code,
     Vhl_1_LH_Drive,
     Vhl_1_Months_Owned,
     Vhl_1_Body_Type,
     Vhl_1_Number_of_Seats,
     Vhl_1_Security_Device_1,
     Vhl_1_Security_Device_2,
     Vhl_1_Security_Device_3,
     Vhl_1_Make,
     Vhl_1_Model,
     Vhl_1_registration_number,
     Vhl_1_Overnight_postcode_1,
     Vhl_1_Overnight_postcode_2,
     Vhl_1_Engine_Capacity,
     Driver_2_Age,
     Driver_2_Sex,
     Driver_3_Age,
     Driver_3_Sex,
     Driver_4_Age,
     Driver_4_Sex,
     ao_youngest_driver,
     so_youngest_driver,
     database,
     Total_Payments,
     Total_Recoveries,
     Total_Estimates,
     Total_Anticipated_Recoveries,
     Total_Claim_Count,
     incurred,
     XOL_recoveries,
     XOL_recoveries_nonNU,
     number_of_capped_claims,
     capped_costs,
     zero_flag,
     claim_count,
     currexp,
     net_earned_premium,
     gep,
     exposure,
     retro,
     pcode_formatted,
     d_sector,
     district,
     Area_code,
     Vehicle,
     policy_suffix,
     Source_System,
     DID,
     Area,
     Payment_MadeFROM

Re: Chart/Graph Dimension Sort/Order on x Axis

SunilChauhan — Mon, 26 Mar 2012 09:33:30 GMT

see the attached file

hopee ths helps

Re: Chart/Graph Dimension Sort/Order on x Axis

Mon, 26 Mar 2012 13:02:09 GMT

Apologies

i have ages 16 - 95. Would i need to create a Load inline for every age?

I also have around 50 other data items. would i need to create a preceeding load to bring in these additional items ??

Thanks for your help

James

Chart/Graph Dimension Sort/Order on x Axis

SunilChauhan — Mon, 26 Mar 2012 13:18:22 GMT

no need to load inline .

you have expression apply in sort expression.

hopee this helps

Re: Chart/Graph Dimension Sort/Order on x Axis

Mon, 02 Apr 2012 15:33:14 GMT

This was suggested to me. But it didnt work. Any Ideas ?

=wildmatch(if(Driver_1_Age<=18,'<18',

if(Driver_1_Age<23,'19-23',

if(Driver_1_Age<28,'24-28',

if(Driver_1_Age<33,'29-33',

if(Driver_1_Age<39,'34-39',

if(Driver_1_Age<45,'40-45',

if(Driver_1_Age<50,'46-50',

if(Driver_1_Age<55,'51-55',

if(Driver_1_Age<60,'56-60',

if(Driver_1_Age<65,'61-65',

if(Driver_1_Age<70,'66-70',

if(Driver_1_Age<=74,'71-74',
'75+')))))))))))),'<18','19-23','24-28','29-33','34-39','40-45','46-50','51-55','56-60','61-65','66-70','71-74','75+')

Re: Chart/Graph Dimension Sort/Order on x Axis

mikecrengland — Mon, 02 Apr 2012 15:41:56 GMT

You could use a 'dual' function in your calcualted dimension

=If (Driver_1_Age<=18, dual('<18',1),

If (Driver_1_Age<23, dual('<23',2),

etc...

Then sort numerically.