Create filter with continuous numbers

[sandp707]

Hello everyone,

How to create a filter based on below conditions.

I have a column with values 1,2,3,4….,52

I need filter based on

if = 1 then delivery 1

if <= 4 then delivery 4 (this should also include 1)

if <= 6 then delivery 6 (this should include both the above conditions)

Thanks

[BrunPierre]

As per the syntax  if(condition , then [, else]) like this

If(Column = 1 ,'Delivery 1',

If(Column <= 4 ,'Delivery 4',

If(Column <= 6 ,'Delivery 6'))) as Filter

[sandp707]

Hello Brun,

I am also using the same logic, but it is showing the results as below.

where for Delivery 1 it is working fine,

for delivery 4 it should show (1,2,3,4) but it is showing (2,3,4)

for delivery 6 it should show (1,2,3,4,5,6) but it is showing (5,6) and same for other numbers

Result:

Thanks

[vinieme12 · Accepted solution]

You need to create a separate table to associate WeekNo with the Deliveries Grouping

So you will have FactTable with Global_Key and WeekNo   and another Table with a one to Many relationship with the Deliveries grouping

as below

You can do it in Two Ways

1)    

FactTable:
Load GlobalKey,WeekNo
From  SomeSourceTable;


WeekGroup:
Load 
WeekNoFrom+iterno()-1  as WeekNo
,Deliveries
While WeekNoFrom+iterno()-1 <=WeekNoTo
;
load * inline [
WeekNoFrom,WeekNoTo,Deliveries
1,1,Last1Week
1,4,Last4Week
1,6,Last6Week
1,13,Last13Week
1,26,Last26Week
1,52,Last52Week
];

Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;

Option 2:

FactTable:
Load GlobalKey,WeekNo
From  SomeSourceTable;


WeekGroup:
Load Distinct WeekNo,'Last1Week' as Deliveries
Resident FactTable
Where WeekNo=1;

Concatenate(WeekGroup)
Load Distinct WeekNo,'Last4Week' as Deliveries
Resident FactTable
Where WeekNo<=4;

Concatenate(WeekGroup)
Load Distinct WeekNo,'Last6Week' as Deliveries
Resident FactTable
Where WeekNo<=6;
.
.and
.so
.on
.for each grouping
.
.

Concatenate(WeekGroup)
Load Distinct WeekNo,'ALL'
Resident FactTable;

[sandp707]

Both the solutions are working fine.

Thanks a lot