Set analysis monthly trend

[keitel2015]

Hi All,

I have a question regarding set analysis. I use this formula to get the revenue for the current or selected fiscal date:

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), ))<=$(=MonthEnd(Max([Fiscal Date])))'}>} [Revenue])

My formula to get the revenue for the month before is this:

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -1))<=$(=MonthEnd(Max([Fiscal Date])-1))'}>} [Recurring Revenue (Euro)])

It works very well but once I selected more than one date in fiscal date (e.g. Oct 2014 and Nov 2014) I don´t get the correct value for the month before my selected date (in this case Sep 2014).

Has anyone a solution?

Thanks.

[maxgro]

try here

Relative Calendar Fields

first expression in that post is like your (if you want you can adapt changing the fields names) but then Henric solved the problem adding a field in the calendar

[keitel2015]

Thanks for your input but unfortunately it isn´t a solution for me.

Let’s assume you have all months of 2013 and 2014. If you click on e.g. Jun and Jul 2014 then your data sets will be decrease on your selection. What I want is to show the sum of Jun and Jul 2014 and the value before my selected date (May 2014).

What I currently have or what you suggested works fine if I only select single dates and no intervals (Jun AND Jul 2014). Because if you click on more than one date your data sets are only what you selected (Jun and Jul) so the formula will only consider Jun and Jul 2014 but not the entire data sets in my application.

For that reason I need a formula which identify the youngest selected date (Jun 2014) and then use the entire data sets to get the value before the youngest date.

[sunilkumarqv]

use selections fields in set analysis like below

sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), -1))<=$(=MonthEnd(Max([Fiscal Date])-1))'},

MonthField=,[Fiscal Date]=,Qtrfield=>} [Recurring Revenue (Euro)])

[#5 · Accepted solution]

do as follows:

[Date]={">=$(=$(vFrom))<=$(=$(vTo))"}>}

create variable vFrom=MonthStart(Max([Fiscal Date]), -1)

vTo=MonthEnd(Max([Fiscal Date])-1)

[keitel2015]

I changed the variables into:

MonthStart(Min([Fiscal Date]), -1)

MonthEnd(Min([Fiscal Date]), -1)

That works for me, thanks