Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
mohedano
Contributor II
Contributor II

Multi bucketing

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 IdDate Acumm 
Emp11/1/2019               10
Emp11/2/2019               13
Emp11/3/2019               25
Emp11/4/2019               36
Emp11/5/2019               58
Emp101/6/2019               60
Emp101/7/2019               74
Emp101/8/2019            139
Emp101/9/2019            177
Emp101/10/2019            290
Emp101/11/2019            300
Emp111/12/2019               99
Emp111/13/2019            127
Emp111/14/2019            171
Emp111/15/2019            226
Emp11/1/2020            597
Emp11/2/2020            735
Emp11/3/2020            992
Emp11/4/2020         1,438
Emp11/5/2020         1,898
Emp201/1/2020               33
Emp201/2/2020               36
Emp201/3/2020               53
Emp201/4/2020               67
Emp201/5/2020               94
Emp301/1/2020               20
Emp301/2/2020               28
Emp301/3/2020               34
Emp301/4/2020               48
Emp301/5/2020               62

 

and the desired output would be:

 

 ClassAcumm (max)
Emp150-10058
Emp11851-1900                  1,898
Emp10251-300300
Emp11201-250226
Emp2051-10094
Emp3051-10062

 

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

Labels (6)
4 Replies
Gui_Approbato
Creator III
Creator III

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
Contributor II
Contributor II
Author

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:

mohedano_0-1594757615205.png

 

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
Creator III
Creator III

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
Contributor II
Contributor II
Author

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!