How to create % Bucket in Dimension

[Deep_C]

Hi All,

I am looking for a URGENT help please.

I have three years data (9mn rows) for which I have to create a table which indicates total clients in by percentage buckets

Current Yr(CY) % - Prev Yr(PY) % = Delta Current Yr(DCY)

Prev Yr % -Prev Prev Yr% = Delta Prev Yr (DPY)

Delta Current Yr (DCY)

DCY_Bucket Clients (similar table for PCY)

-100%- -50%  200

-50% to-0    500

0-50%          700

50-100%     600

For this I have created another small date table in load script to tag Current Yr, Previous Year and Prev. previous Yr

// Illustration due to data confidentiality 

Main_Table:

TIME_ID,

Clients,

SALES

from table a;

Calendar:
LOAD 2300 as TIME_ID,
1 as CURRENT,
1 as TYPE_FLAG
Autogenerate(1);

CONCATENATE
LOAD 2229 as TIME_ID,
1 as PREVIOUS,
2 as TYPE_FLAG
Autogenerate(1);

CONCATENATE
LOAD 2267 as TIME_ID,
1 as PPREVIOUS,
3 as TYPE_FLAG
Autogenerate(1);

Below expression works if I keep it in expression with Clients in Dimension 

sum(SALES*CURRENT)-sum(SALES*PREVIOUS)

But if I try to switch in Dimention, it doesnot work

e.g.

Dimension 

if(  sum(SALES*CURRENT)-sum(SALES*PREVIOUS) >=0  and sum(SALES*CURRENT)-sum(SALES*PREVIOUS)<50, '0 - 50%',

if(  sum(SALES*CURRENT)-sum(SALES*PREVIOUS) >50  and sum(SALES*CURRENT)-sum(SALES*PREVIOUS)<100, '50 - 100', '<0'))

Expression : Count(Distinct(Clients))

please help with suggestion

1. How to write such condition in Dimension? OR

2. Should I change load script to create delta %? how?

[chrismarlow]

Hi,

I think you need to use AGGR & I think there are a few things on the community that could be used as a start point, maybe this;

Solved: How to create buckets using percentage value - Qlik Community - 1374874

Cheers,

Chris.