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?
