Previous Month Sales

[1Hunter]

Hi everyone,

I am facing the issue below:

I am trying to show in the same table 3 columns:

- MonthYear(Date(text, 'MMM YYYY')

-Sales

-"Sales for previous MonthYear" based on the MonthYear showed in the first column.

The issue is that if I do not select a MonthYear I get only 0 for "Sales for previous MonthYear".

How can I show in the same table the Date, Sales and the "Sales for previous MonthYear", without to use Above?

If is possible I am looking for a set analysis expression..

[stevejoyce · Accepted solution]

Not sure why Above would return 0 without selections.  if current sales expression is Sum(Sales).  Previous month would be Above(Sum(Sales)).

You can't use set analysis to change row level context for the same dimension in an object.  If Above() doesn't work, i've had to do this by creating a seperate month field that bridges to fact data many-many with flags.  So February 2021, would associate to both Feburary 2021 facts and January 2021.  Your bridge table can have a flag like _IsCurrent where Feb = 1, Jan = 0 in this case.  And your set analysis in our object for current calculations have {<_IsCurrent ={1} >} and Previous would have {<_IsCurrent ={0} >} 

[1Hunter]

@stevejoyce, thanks a lot for your idea.

I am working on it, now I have the tables below:

How can I join them in a single table?

Eg:

MonthYearCurrent, MonthYearPrevious,MonthYearFlag:
Jan 2019, Jan 2019, 1
Jan 2019, Dec 2018, 0

The code that I am using to generate the tables:

CalendarNew1:
Load Distinct 
Autonumber(MonthYear) as CalendarKey,
MonthYear as MonthYearCurrent, 
1 as MonthYear_Flag1,
DualMonthYear
Resident SalesData
Order by DualMonthYear asc;
Drop field DualMonthYear from CalendarNew1;



CalendarNew2:
Load Distinct 
Autonumber(MonthYear) as CalendarKey,
Date(AddMonths(MonthYear,-1),'MMM YYYY') as MonthYearPrevious, 
0 as MonthYear_Flag2,
DualMonthYear
Resident SalesData
Order by DualMonthYear asc;
Drop field DualMonthYear from CalendarNew2;

[stevejoyce · Accepted solution]

I think your 2nd table should have a -1 on the key... but if you just concatenate the 2 together and rename your Calendar2 table with the same names as Calendar1, just MonthYear and MonthYear_Flag.  Assuming your facts have the CalendarKey, you should now have 2 rows per month wiht your flag for current and previous.

So you'll have:

key, monthyear, flag

2, February 2019, 1

3, February 2019, 0

(again i think Feb 2019 should be 2 & 1, not 2 & 3)

where Feb 2019 is associated to 2 months.  now you can use your flag in your set analysis

Your rows for Feb 2019 will now be associated to 

[1Hunter]

Hi @stevejoyce ,

Thanks a lot for your support.

I created the table from the following screenshot:

The key from my data model is called MonthYear, which is 'MMM YYYY'.

Which column from the new table should be linked with the key from the data model?

I tried already with both of them(renaming each one of them into MonthYear), setting the flag in expression, but both expressions are showing the current month sales.

sum({<PreviousMonthFlag={1}>}PTS) and also,
sum({<PreviousMonthFlag={0}>}PTS)

[stevejoyce · Accepted solution]

Based on the data, MonthYearCurrent would be your filter or object dimension.  Join to fact table by MonthYearPrevious.  And your expressions should work.

sum({<PreviousMonthFlag={1}>}PTS)

and also,

sum({<PreviousMonthFlag={0}>}PTS)

[1Hunter]

@stevejoyce, thank you so much for your guidance!

Everything works perfectly! Just to clarify, I cannot use above because I need to calculate the evolution of the %growth based on the MonthYear dimension and also on a 2nd dimension which is the Brand therefore it would calculate it only for the 1st value in Brand, not for all that are available..

Thank you again!!