Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I need to create an adhoc report which has multiple dimensions and expressions.Out of all expression in one of the expression i needed to calculate sales of Current month - Previous Month.
Eg. If i select branch it will show all branch by default with Current / Max month sales - Previous month sales.
Now in list of dimensions i have Year and month as well. For Year by default it should show all Years with following conditions
Eg. I have two year then it should show
Year Sales
2017 Max(Month)-Max(Month)-1
2016 Max(Month)-Max(Month)-1...
and For Month it should show all months data by default with following conditions -
Eg. considering my
July July's Sales - June's Sales (for all years since no year selected)
June June's Sales-May's Sales ...and so on.
How can i achieve this ? Attaching a sample data to work around
Thanks In advance.
Hi Milind,
You have to create a variable as
Let vLastMonthsale = Sum({<Month = Month(Today())>}Sales)
Let vThisMonthsale = Sum({<Month = AddMonths(Month(Today())-1>}Sales)
Now use this formula for your scenario.
July July's Sales - June's Sales (for all years since no year selected)
June June's Sales-May's Sales
You have do this for rest of the month and you can go up till end of last year.
Many Thanks
Karthik
Hi Karthik,
Thnx for your reply but I am not getting desired output
Hi Milind,
You can use below script to calculate variance at Monthyear,
Aggr_MOnth:
LOAD
MonthYear,
Sum(Sales)
FROM [lib://SalesData/Sales.qvd]
(qvd) Group By MonthYear
Order by MonthYear;
LEFT JOIN(Aggr_MOnth)
load
MonthYear,
Previous(Sales) as PMSales
Resident Aggr_MOnth;
FinalSalesVariance:
Load
MonthYear,
(Sales - PMSales) as SalesVariance,
PMSales,
Sales
Resident Aggr_MOnth;
Drop table Aggr_MOnth;
Note:
You can also have others data gran in the group by
Regards,
Nirav Bhimani
Hi Nirav,
Thnx for reply. Will check and let you know.
Hi Milind,
Sorry, try using this
Let vLastMonthsale = Sum({<Month = {$=(Month(Today()))}>}Sales)
Let vThisMonthsale = Sum({<Month = {$=(AddMonths(Month(Today())-1)}>}Sales)
or trying using Nirav solution but I personally prefer not to bring in the aggregation down to script due to impact on load efficiency.