Previous year % comparison

[jlampard40]

Hi guys - I need to produce a graph which shows month on month % compliance with the previous year?  So, as example, Nov-20 against Nov-19 expressed as a %.  Due to COVID we need to see how we are recovering on a monthly basis.

The field we use to count is called AD_EventKey and the date field for Calendar month is called AD_CalendarMonth.  Can you help guys?

Many thanks - rgds.

[edwin]

create an expression in the BACKGROUND COLOR option for the expression

[jlampard40]

@edwin  - many thanks!  I have this expression working OK, but I also want to make the current month column 50% transparent as it's still a month which is building, in terms of data, and so not really representative to show as an actual column in the chart.  I want it to always be the current month which is 50% transparent compared to the rest, how can I achieve this? 

The formatting I have so far is this:

=if(

count({<DateType={'CURRENT'}, Activity_or_Demand={'Activity'}>}Event_Key)/count({<DateType={'PRIOR'}, Activity_or_Demand={'Activity'}>}Event_Key)

>=1.0, Green(), Red())

Seems to work well for the colour formatting of ALL columns, just need the 50% 'current month' column as part of the expression...?

Many thanks and hope you're having a good day?  Kind regards

[edwin]

you will need a couple of things:  first a way to identify current month or period (typically i would use a variable as computing this in the expression makes it complicated), then in your expression, test if it is current, then you use and rgb of the lighter color you want:
if( count(...)<=1
       if(test here for current month, rgb(of lighter green), green),
       if(test here for current month, rgb(of lighter red), red)
)

try that first