topic Re: Previous month figures in QlikView

Previous month figures

guillaume_gorli — Sat, 04 Mar 2017 23:38:52 GMT

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

Re: Previous month figures

sunny_talwar — Sun, 05 Mar 2017 01:34:17 GMT

Try this:

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

Re: Previous month figures

sunny_talwar — Sun, 05 Mar 2017 01:38:29 GMT

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

Re: Previous month figures

guillaume_gorli — Sun, 05 Mar 2017 16:25:37 GMT

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

As always you are big help

Guillaume