How to setup column chart only for last 3 months


#1

Hello,

I am trying to design very simple column chart that shows data for last three months from selected Invoice date. But we need to show two column in X axis (Market and Posting Month) and Total volume of invoices on Y axis. something like below.

Challenge I am facing it should be considering last three month from the Max invoice Date month.
I tried RANGE and ADD_MONTH functions but not able to get through the challenge. Could anyone pls help me here?

Just for illustration we can use below data:
Market | Month | Volume
Ghana Feb2019 1258
Ghana Jan2019 20
Ghana Dec2018 33
Ghana Nov2018 11
Ghana Oct2018 11
USA Feb2019 200
USA Jan2019 117
USA Dec2018 34
USA Nov2018 87
USA Oct2018 101


#2

Hi,

I recommend apply this component filter to your plot.

FILTER DATEDIFF(mm, "your table"." invoice Date " , TODAY()) < 3;

It filters out all cases where the number of months between the invoice date and today is more than 3. Note this function doesn’t round the months so if the invoice date was more 3 months and 15 days ago DATEDIFF would return the result 3.5.

Best Regards,
Viana


#3

Hello Viana,

Thanks for your input. I have added above statement in component filter setting however I am now facing challenge is it shows 3 months data for all markets and its difficult to understand the pattern of 3 months for any given market. example- in below screenshot colors of all columns are same. Is it possible to specify different color for each month? Market and Month(invoice date) are my dimensions and count of cases as my KPI.


#4

Hello,

Yes it is possible. Here is how I’d do it.

You can change the colours of your graph by, opening the component options for you graph and choosing “Data series: Case Count” from the drop down on the top. This will take you to the graphical options menu

Then click on the little triangle next to “choose a pallet”. Each colour in the pallet you choose will applied to one column in order, if you have more columns than the palette has different colours then it will start again from the beginning. Since you have 3 months for each Market, I recommend you chose a pallet with containing 3 colours. If you don’t like the palettes on offer you can design your own by clicking on “New palette” and adding any three colours you like

Best Regards,

Viana


#5

Hi Viana,
I tried you suggestion and it is working in most of the cases however in some cases it changes the order of the color or whenever we have no data for any month for particular market it start to shuffle the coloring order. in below example- For Ghana it is not following the color order (green-blue-orange) and in in Italy where we have data only for two month it changed the order.

Is there any way I can specify my current month as Orange last month as blue and month before last month as green?


#6

Hello
Can anyone confirm if this is a bug or I am missing some steps?


#7

Hi,

To fix the order of the colors, select the option “Palette colors based on sorting”.
As for markets with no data for a specific month, unfortunately, it is currently not possible to skip colors.

Best,
Viana