Previous month figures

[guillaume_gorli]

Dear All,

i have a database showing date and sales.

i would like to show in a text box the sales of the previous month depending on the month selected by the user.

No issue to get the previous year figure but i am struggling with the previous month formulas.

For instance, for Jan 2017 as the period selected, the output of the sales M-1 text box should be 21 (as 21 is the sales of Dec 2016)

Attached is qvd and excel test database for the ones you mignt want to help

Thanks in advance

Guillaume

[sunny_talwar]

Try this:

=Sum({<Année_Mois = {"$(=MonthName(Max(Année_Mois), -1))"}, Mois, Année>}Sales)

[sunny_talwar · Accepted solution]

Another (and probably a slightly better option is to use this)

=Sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM YYYY'))"}, Mois, Année>}Sales)

Here MonthYear is created using Date() function rather than MonthName() function

Calendrier:

LOAD MaDate as Date,

Year(MaDate) as Année,

'Q ' & Num(Ceil(Month(MaDate)/3),'(ROM)0') as Trimestre,

Month(MaDate) as Mois,

MonthEnd(MaDate)as DateFinMois,

Week(MaDate) as Semaine,

Weekday(MaDate) as JourSemaine,

Day(MaDate) as Jour,

MonthName(MaDate) as Année_Mois,

Date(MonthStart(MaDate), 'MMM YYYY') as MonthYear

RESIDENT ChampDate;

Date() works better in set analysis when doing ranges compared to MonthName. So if you would want to do range (for instance 6 months) then Date will work much better here.

Best,

Sunny

[guillaume_gorli]

Thanks a lot Sunny, This is exactly what i was looking for.

As always you are big help

Guillaume