Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
My requirement is to get Total Amount as percentage in text box for MTD Vs LMTD, Current Month Vs Last Month, Daily , Monthly.
I have to show result like below in text box
Total Amount for daily : 0.00%
Total Amount for monthly : 0.00%
Total Amount for MTD Vs LMTD : 0.00%
Total Amount for current Vs previous month: 0.00%
Kindly help me out to write the percentage expression for MTD Vs LMTD, Current Month Vs Last Month, Daily , Monthly.
Thanks !!!!
Hello ,
Try using Set Analysis equations like :
Yearly Change ie, Total Amount for YTD Vs LYTD :
(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current YTD ,
Sum({<Date={">=$(=num(AddYears(YearStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year YTD .
then you can use :
[ (Current YTD-Last Year YTD)/Current Ytd ] *100 => this will give you Yearly Increase in %.
Monthly Change ie, Total Amount for MTD Vs LYMTD :
(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current MTD ,
Sum({<Date={">=$(=num(AddYears(MonthStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year MTD .
Then you can use :
[ (Current MTD - Last Year MTD)/ Current MTD ] * 100 = > MTD vs. Last Year MTD in %.
For Daily Change :
(Sum({<Date={"$(=num(MAX(Date)))"}>}Sales_Amt) => Today's Sales Total ,
(Sum({<Date={"=$(=num(MAX(Date)-1))"}>}Sales_Amt) => Yesterday's Sales Total.
Then You can use :
[ (Today's -Yesterday)/Today's ]*100 => Daily Change in Sales.
You can extend these formulae to get Current vs Previous month. Current week vs Previous Week by slight changes in the range of date in the Set Analysis statements .
Let me know if you get the desired results or if any query arises.
Thanks & Regards.
Hello ,
Try using Set Analysis equations like :
Yearly Change ie, Total Amount for YTD Vs LYTD :
(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current YTD ,
Sum({<Date={">=$(=num(AddYears(YearStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year YTD .
then you can use :
[ (Current YTD-Last Year YTD)/Current Ytd ] *100 => this will give you Yearly Increase in %.
Monthly Change ie, Total Amount for MTD Vs LYMTD :
(Sum({<Date={">=$(=num(YearStart(max(Date)))) <=$(=num(MAX(Date)))"}>}Sales_Amt) => Current MTD ,
Sum({<Date={">=$(=num(AddYears(MonthStart(max(Date)),-1))) <=$(=num(AddYears(MAX(Date),-1)))"}>}Sales_Amt)) = > Last Year MTD .
Then you can use :
[ (Current MTD - Last Year MTD)/ Current MTD ] * 100 = > MTD vs. Last Year MTD in %.
For Daily Change :
(Sum({<Date={"$(=num(MAX(Date)))"}>}Sales_Amt) => Today's Sales Total ,
(Sum({<Date={"=$(=num(MAX(Date)-1))"}>}Sales_Amt) => Yesterday's Sales Total.
Then You can use :
[ (Today's -Yesterday)/Today's ]*100 => Daily Change in Sales.
You can extend these formulae to get Current vs Previous month. Current week vs Previous Week by slight changes in the range of date in the Set Analysis statements .
Let me know if you get the desired results or if any query arises.
Thanks & Regards.