Dynamic Month-Year comparison with previous Month year in line chart

[sa76269]

Hi, 

So, my dashboard works on giving Total policy premium on the basis of Month-Year on x-axis. The user can select a start and end Month-year. E.g. He/She can chose Jan-2018 till Dec-2019, which will show up  on x-axis with sum of premium as line chart for that time duration. The user can chose a total duration of any number of Month-Years ( >1 year). 

I calculate sum of premium as 

Sum(if((Date>DateFrom) and (Date<DateTo),GrossPremiumAmt))

Here, Date is the actual date as in data (MM-YYYY). DateFrom, DateTo are constants selected by user.

Now I want a previous year line chart over current year. So, if person selects Jan-2018 till Dec-2019, so the previous year line graph will have Jan-2017 total premium over Jan-2018 and so on for 2 years.

I tried using set analysis, but didn't quite work for me. Could anyone give any direction or solution to this?

[JordyWegman]

Hi,

You can try the following formula:

Sum(if((Date>(DateFrom-365)) and (Date<(DateTo-365)),GrossPremiumAmt))

Jordy

Climber 

[sa76269]

Hi @JordyWegman, I already tried this.

But since this 'Date' field itself is a dimension (x-axis), the PY chart will always be zero. This is because,

1. If total duration is 1 year, Date with never lie between (Datefrom-365) and (DateTo-365). e.g. for, Jan-2018 to Dec-2018, you are checking if Jan-2018 lies between (Jan-2017, Dec-2017), which is false.

2. If total duration > 1 year, it'll always give middle year sums. e.g. for Jan-2017 to Dec-2018, it'll only populate a graph for Jan'2017-Dec'2017. ( coz comparing date of given interval between Jan-2016 to Dec-2017)

[JordyWegman]

Hi,

Then you need to use set analysis. You need to create variables for you DateFrom & DateTo.

Sum({$< DateFrom = ">='$(DateFromVariable)')",DateTo = ">='$(DateToVariable)')">}GrossPremiumAmt)

Sum({$< DateFrom = ">='$(DateFromVariableLY)')",DateTo = ">='$(DateToVariableLY)')">}GrossPremiumAmt)

Jordy

Climber