Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have a Demand from our Marketing Department. the want to see the sales of a given period this year Vs. The same period the previous year. Ivé attached an example file. The first column is all the dates of current year, the second column is the dates last year, the third column is the national holidays(Jewish & Islamic) and the forth column is holidays periods.
Now, the date this year Doesn't always match the date of last year, more than that, January of last year matches Jan. & Feb. this year.
The users have to be able to analyze the time period both with the gregorian calender(Represented today by the master calendar) and with the holidays calendar(Represented in this file)
Any Ideas?
hi
attach is an example , with a concept you asked for
you'll see 3 KPI :
sales for selected period
sales previous year
sales according to the file
Thank you for you effort.
Can you please explain the concept?
HI
the concept is under this assumption we have one date field in the fact table , we'll use it for all calculations.
first step is to build the calendar table base on the attach excel file
the concept is to assign to each date :
1.previous year date created by using addmonts function
2. same that last year part of the base table
3. now the tricky part because several dates can have the same date in the field same date last year , we need to calculate multiplier to each date according to the same date last year
this is done by this script part
Multipliers:
load [Same Date Last Year] as Date,
count([Date]) as multiplier
resident Dates
Group by [Same Date Last Year];
please note the result is connected to the original date field but is based on the same date last year field
now you can build your KPI's
1. current year - sum(Sales)
2.previous year - Sum({<[Holiday Period]=, Date = p(PrevYear)>} Sales)
some notes - you can add other fields you need to disregard selections
the reason to use p statement in the set analysis is support to any type of selection , range selection or specific dates selections
3. previous year with a twist - Sum({<[Holiday Period]=, Date = p([Same Date Last Year])>} Sales*multiplier)
this expression use the multiplier to cover the case you need to use the same date more than once
Tank you.
I will look Into it.