Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try this:
=Sum({<Année_Mois = {"$(=MonthName(Max(Année_Mois), -1))"}, Mois, Année>}Sales)
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
Thanks a lot Sunny, This is exactly what i was looking for.
As always you are big help
Guillaume