Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I've taken on a few dashboards and I am trying to understand some of the expressions. Would anyone be able to explain in simple terms what these expressions are actually doing?
YTD Sales:
sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)
Average intake:
sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart)))
Predicted YTD:
=(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart))))*365
Av intake req:
(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}TARGET)-sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES))/(365 - (date#(today())-date#($(vYrStart))))
Target:
=sum({<FINMONTH = , FINYEAR = {'$(=max(FINYEAR))'}, CAL_MONTH_STR=>}TARGET)
TIA
ok
YTD Sales:
sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)
This is a Sum of SALES with the following criteria
FINYEAR is the maximum FINYEAR (taking into account your current selections)
FINMONTH is greater than max(FINMONTH) minus max FINMONTH (which is completely pointless as it will always be zero!) and is less than 12
Average intake:
sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart)))
Is YTD Sales, but divided by today() minus the variable vYrStart, so I would expect thias to return an average daily figure here
Predicted YTD:
=(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES)/(date#(today())-date#($(vYrStart))))*365
This is the Average intage multiplied by 365 to project a figure for the whole year
Av intake req:
(sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}TARGET)-sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH = {'>$(=max(FINMONTH)-max(FINMONTH)) <= 12'}>}SALES))/(365 - (date#(today())-date#($(vYrStart))))
This the difference between YTD Sales and Predicted YTD
Target:
=sum({<FINMONTH = , FINYEAR = {'$(=max(FINYEAR))'}, CAL_MONTH_STR=>}TARGET)
this is simply a SUM of TARGET for the current FNYEAR
Marcus thank you!
Are you able to advise how I can get a networkdays calculation for the average intake? This will then allow me to do more accurate reporting as orders are not received on weekends etc.
I'm not sure here but you *may* have an issue with your Average Intake, Predicted YTD, and Av Intake Req if you were to select a prior FINYEAR.
YTD Sales will correctly return the result for the FINYEAR selected, however your divisor may be incorrect,
This is because of the (date#(today())-date#($(vYrStart)) calculation. today() will evaluate to 6th September 2017, and I presume vYrStart will evaluate to the year start of the selected FINYEAR.
ok, based on the above expression, which as noted I believe is flawed!
Average Intake would be something like
sum({<FINYEAR = {'$(=max(FINYEAR))'}, FINMONTH= >}SALES)/
networkdays(date#($(vYrStart)), (date#(today()))
I have simplified the FINMONTH part, as I am assuming that you have no FINMONTHs below zero or above 12, so basically we can ignore selections in that field
Also I have divided by Networkdays between today and whatever is being returned by your vYrStart variable
perfect!
I've managed to create a few variables and it's now working much better thanks for your help.