Previous Year Sales

[twinklea]

Hi,

I need the calculation of total sales for the previous year irrespective of the MonthYear dimension in a table.

Below is the scenario:

MonthYear	Sales	Previous Year Total Sales
Jan-18	300	1000
Feb-18	730	1000
Mar-18	400	1000
Apr-18	450	1000
Jan-17	100	1460
Feb-17	200	1460
Mar-17	300	1460
Apr-17	400	1460
Jan-16	320	0
Feb-16	180	0
Mar-16	460	0
Apr-16	500	0

Suppose for Jan2018 to Apr 2018 , it should calculate the sales from Jan 2017 to Apr 2017 and result as 1000 for all the 4 records and similar way for other years.

Please note that this output should also reflect filters from the dashboard.

Request the solution asap.

Regards,

Twinkle A

[martinpohl]

Hi,

load your datas, add a field Year.

then add script:

YearSum:

load 

Year,

sum(Sales) as YearSum

resident YourData

group by Year;

left join (YourData) load

Year+1 as Year

YearSum as "Previous Year Total Sales"

resident YearSum;

drop table YearSum;

Hope this helps, 

regards

[twinklea]

Hi Martin,

I will try this solution and get back but i was also looking for this to be achieved using set analysis with sum and aggr function. I was not successful but if anyone can provide the solution with set analysis, it will be useful performance wise too. 

Regards,

Twinkle A

[twinklea]

Hi Martin,

The script solution mentioned is not working correctly.

Regards,

Twinkle A

[sunny_talwar]

I think the best way to handle this would be to use The As Of Table

[twinklea]

Hi Sunny,

I tried using AsOfTable but not working out. May be I am missing out something here. Can you help with this example?

[twinklea]

Hi Sunny,

Is it possible for you to help me out with this issue of getting previous year total sales with the dimension of monthyear.

I have used the below SET ANALYSIS:

sum(aggr (sum({<Type={'Previous'},CalMonthYear=,Month_Temp=>} [Qty ()]),AsOfYear))

where AsOfYear & Month_Temp is my dimension

With this, I am getting the below output:

MonthYear	Sales	Previous Year Total Sales
Jan-18	300	1000
Feb-18	730
Mar-18	400
Apr-18	450
Jan-17	100	1460
Feb-17	200
Mar-17	300
Apr-17	400
Jan-16	320	0
Feb-16	180
Mar-16	460
Apr-16	500

The total previous year value is coming but it showing only for the January row data which I want to be shown for rest of the months too the same values like 1000 should appear for all 4 months of year 2018.

Let me know please what i am missing here.

Thanks in advance.

[sunny_talwar · Accepted solution]

You can try one of these

Sum(Aggr(NODISTINCT Sum({<Type = {'Previous'}, CalMonthYear, Month_Temp>} [Qty ()]), AsOfYear))

or this

Sum(TOTAL <AsOfYear>{<Type = {'Previous'}, CalMonthYear, Month_Temp>} [Qty ()])

[twinklea]

Hi Sunny,
The first expression with Nodistinct function is giving me the same values for all the months which i need but t is somehow showing the double values. For example, instead of 1000, I am getting 2000 for all the required records.
The second expression is drilling the result to the month level .
I guess the first expression needs to be more refined to get the correct output.

Let me know if you can suggest something on this.

[sunny_talwar]

Would you be able to provide a sample to check this out?