I am a new user to Qlik, and am having trouble defining buckets in QlikSense. I am trying to create a bucketed bar chart that displays shipment data.
The x-axis would display weight ranges (e.g., '0-1 kg', '1-10kg', etc.) and the y-axis would display the % of shipments in each weight range. I would like each weight range to contain three vertical bars:
One for shipments completed in the most recent month (i.e., April 2015)
One for shipments completed 12 months previous to the most recent month (i.e., April 2014)
One for the previous rolling 12 months (i.e., May 2014 - April 2015)
My data source contains the date and total weight for each shipment line item (e.g., Shipment Number, Shipment Date, Shipment Total Weight).
I have tried doing this using several methods, including using SQL to pull three additional columns that give the total weight for shipments that fall into the three temporal categories above, with 0 or Null when they do not, so that I could use a simple count/sum function in QlikSense instead of writing complicated expressions (column names are CurrentMoShipWgt, PreviousMoShipWgt, Previous12MoShipWgt). However, QlikSense counts the Null values when I do this and I end up with a huge Null bucket (unticking 'show null values' doesn't work). I am also unclear how to create the chart so that all three bars would be displayed.
Does anyone have any ideas for how to do this efficiently? I can definitely update my SQL query that I use to pull the data into Excel if this is easier than manipulating the data in QlikSense.