Multi bucketing

[mohedano]

hi all, 

I hope you'll find this a bit of a challenge since I already spent a couple of days trying to figure out how to multi-bucket in Qlik. 

My case:

• I have the following (simplified ) dataset: Emp Id, Date and Acumm. Accum is simply the Sales accumulation as of a given date. 
• Users can choose any  bucket (class) size, via input text object. 
• Users can select two different periods of time (P1 and P2) to compare them against each other. there's a Date field for P1 that exists in the Fact table, and there's also a Date_2 field that exists as an island in the data model. 

the challenge is that we need to get the max Accum value per Emp and per each Period, so some employees could have two max values: one Max value for P1 and another max value for P2. Other employees could have only one max value, in either P1 or P2, given that they didn't have Sales for one of the two periods. 

here a 'sample' data set:

Emp Id	Date	Acumm
Emp1	1/1/2019	10
Emp1	1/2/2019	13
Emp1	1/3/2019	25
Emp1	1/4/2019	36
Emp1	1/5/2019	58
Emp10	1/6/2019	60
Emp10	1/7/2019	74
Emp10	1/8/2019	139
Emp10	1/9/2019	177
Emp10	1/10/2019	290
Emp10	1/11/2019	300
Emp11	1/12/2019	99
Emp11	1/13/2019	127
Emp11	1/14/2019	171
Emp11	1/15/2019	226
Emp1	1/1/2020	597
Emp1	1/2/2020	735
Emp1	1/3/2020	992
Emp1	1/4/2020	1,438
Emp1	1/5/2020	1,898
Emp20	1/1/2020	33
Emp20	1/2/2020	36
Emp20	1/3/2020	53
Emp20	1/4/2020	67
Emp20	1/5/2020	94
Emp30	1/1/2020	20
Emp30	1/2/2020	28
Emp30	1/3/2020	34
Emp30	1/4/2020	48
Emp30	1/5/2020	62

and the desired output would be:

	Class	Acumm (max)
Emp1	50-100	58
Emp1	1851-1900	1,898
Emp10	251-300	300
Emp11	201-250	226
Emp20	51-100	94
Emp30	51-100	62

where  

P1: Jan 2020

P2: Jan 2019

* users can select any date period. March 2020 vs April 2020 or QTD vs Current Month, etc. 

on the table above you can see that Emp1 belongs to two classes and the other Employees only belong to one. Emp1 is just one of many cases where a Employee could be in 2 different class/buckets. 

hope you find this challenging (and clear)

thanks for your help

Carlos

[Gui_Approbato]

Hello Carlos

I used an "alternate state" approach.

Check the file to see if it matches the requirements.. The 'issue' is that I used two tables instead of one, but if you filter 2020 in Year1 you will get the desired result.

Regards,

[mohedano]

hi Gui,

thank you for the time you spent on this, unfortunately I need one single table since it'll be 'base' for the Dimension for a line chart. 

here's the very final expected result:

the table above (modified to be shared here) is what I have so far in Qlik Sense. The problem with this chart is that, for example, Emp1 is showing up in the 1851-1900 Class in both lines, P1 and P2. Each line is basically the same expression (Emp Count) but using different set analysis to reflect  P1 and P2 date ranges.

Ideally, (for example)  Emp1 would show up in the 50-100 Class for the P2 line and in the  1851-1900 class for P1. Emp1 desired locations are shown in green in the chart above. 

thanks! and apologies if I wasn't clear enough in my initial post.

[Gui_Approbato]

Hi Carlos,

I got really closed to the expected result now using set analysis and also the alternate state.

Basically you will be able to keep both filters using the alternate state, but in the table you will get that expected result.

I used Valuelist to create a new field and a condition to match the expression.

After creating a column with Valuelsit('P1','P2'), the main expression was:

if( ValueList('P1','P2')='P1',

Max(Aggr(

Max(Acumm),[Emp Id],Date)),

Max( {P2<[Emp Id]=$::[Emp Id],Date=$::Date,Acumm=$::Acumm>} Aggr(

Max( {P2<[Emp Id]=$::[Emp Id],Date=$::Date,Acumm=$::Acumm>} Acumm),[Emp Id],Date)))

I don't quite get your chart (considering the data we got here) but I believe you will understand the expression and hopefully you can adapt and get there. Not sure if valuelist is the most adequate one, but check it out there 🙂

See file attached.

Best regards

[mohedano]

hey, 

thanks for sharing... I still can't get it to work in your app, but I think you've given a new idea: "valuelist()". I'll give it another shot but this time using valuelist too. 

In a few hrs I'll share a qvf that resembles more to  my actual case, hopefully that'll give you (and others) a much better idea of what's going on. 

thanks!