topic Re: Time Bucket in QlikView

Time Bucket

Anonymous — Mon, 10 Aug 2015 07:53:24 GMT

Hi All,

I have Created time bucket as shown

if(DueDays<0,'Not Due',if(DueDays<=30,'0-30d',if(DueDays<=60,'30-60d',if(DueDays<=90,'60-90d',if(DueDays<=90,'90-120d','>120d'))))) as TimeFrame

but when I use this in Chart as dimension it is not in proper order...

I want it to be in Following order

Not Due, 0-30d, 30-60d, 60-90d, 90-120d, >120d...

how to do this......

LOAD *, if(DueDays<0,'Not Due',if(DueDays<=30,'0-30d',if(DueDays<=60,'30-60d',if(DueDays<=90,'60-90d',if(DueDays<=90,'90-120d','>120d'))))) as TimeFrame;
Load * INLINE [
Customer, OverDue, DueDays
Cori Engineers, 323469,48
AA Steel, 491678,32
Netbau, 181200,78
Cary Inc,536215,150
National Envelope,456432,86
Pinnacle Frames,653234,12
Key Technologies,345675,62
Senderex Cargo,123456,41
BayRing Communications,987346,163
Horizon Offshore Contractors,352418,-48
BoooYa Properties,451287,18
IntruGuard Devices,128594,75
Southern Illinois University,136248,36
Canadian Medic,852481,-42
Lattice Semiconductor,763865,85
St Francis Hospital,152486,14
National Logistics ,669524,96
Support Center US Datacomm,336524,156
Diocese Rockville Centre,158962,52
Next Day Blinds,599635,84
US Joint Forces Command,61
];

Re: Time Bucket

sushil353 — Mon, 10 Aug 2015 07:56:37 GMT

Hi,

Load an inline table:

Load * inline

[

TimeFrame,SortField

Not Due,1

0-30d, 2

30-60d, 3

60-90d, 4

90-120d, 5

>120d...,6

];

Now in your chart sort by SortField

2. Using Dual function as:

if(DueDays<0,Dual('Not Due',1),if(DueDays<=30,Dual('0-30d',2),if(DueDays<=60,Dual('30-60d',3),if(DueDays<=90,Dual('60-90d',4),if(DueDays<=90,Dual('90-120d',6),Dual('>120d',7)))))) as TimeFrame;

HTH

sushil

Re: Time Bucket

MayilVahanan — Mon, 10 Aug 2015 07:57:54 GMT

Try like this

LOAD *, if(DueDays<0, Dual('Not Due',0),if(DueDays<=30,Dual('0-30d',1)

,if(DueDays<=60,Dual('30-60d',2),if(DueDays<=90,Dual('60-90d',3),if(DueDays<=90,Dual('90-120d',4),Dual('>120d',5)))))) as TimeFrame;

Re: Time Bucket

dusasuman — Mon, 10 Aug 2015 08:03:36 GMT

In chart properties- go to sort -> Check numeric values as Ascending, Text as A-Z...

If it doesn't work properly

use Sort by expression.

In expression block write the code: Wildmatch(DueDays,'Not Due', '0-30d', '30-60d', '60-90d', '90-120d',' >120d')

Hope it helps

-SUMAN

Re: Time Bucket

Anonymous — Mon, 10 Aug 2015 08:03:39 GMT

Thanks.. it worked.

Re: Time Bucket

qlikviewwizard — Tue, 11 Aug 2015 01:55:45 GMT

Hi MayilVahanan

Nice answer. Thank you.