Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
There are 2 things I am trying to go but it looks I need a bit help:
I'm trying to build a dashboard for my sales team, I'm connected to db where the timestamp of my orders is mentionned in OrderDate
1. I want to get the sales of the ongoinf year but the formula below returns '0'
Sum({$<OrderDate = {$(=year(Max(OrderDate)))}>} Sales) - What am I doing wrong here?
I have to uses Max(OrderDate) instead of today() As I am working with sample data from 2008
2. I also want to compare the data of sales of the ongoing year with the data of the year before at the same day
For example: On 1/6/2008 I want the total of sales from 1/1/2008 until 1/6/2008 but I also want to know the sales of 1/1/2007 until 1/6/2007
Thank you for your help
Tom
you are trying to compare a date field with a year, so is like comparing 08/20/2015 to 2015
this will give you the info for the last 12 months of your max date
Sum({$<OrderDate = {">=$(=addmonths(Max(OrderDate),-12)) <= $(=(Max(OrderDate)))"}>} Sales
if you want to only show from the beginning of the year instead of the last 12 months you can change addmonths for yearstart
Sum({$<OrderDate = {">=$(=yearstart(Max(OrderDate))) <= $(=(Max(OrderDate)))"}>} Sales
See this examples
sorry but it doesn't seem to work, if I use Sum({$<OrderDate = {">=$(=yearstart(Max(OrderDate))) <= $(=(Max(OrderDate)))"}>} Sales
I get "-" as a result
I missed the closing ")" in my expression
Sum({$<OrderDate = {">=$(=yearstart(Max(OrderDate))) <= $(=(Max(OrderDate)))"}>} Sales)
Hi,
Try this expressions
For Current Year:
Sum({$<OrderDate = {">=$(=Yearstart(Max(OrderDate))) <= $(=Date(Max(OrderDate)))"}>} Sales)
For Previous Year:
Sum({$<OrderDate = {">=$(=Yearstart(Max(OrderDate), -1)) <= $(=YearEnd(Max(OrderDate)))"}>} Sales)
Hope this helps you.
Regards,
Jagan.
Thanks Ramon and jagan,
the current year expression is perfect,
however the previous year does not seem right, if I use jagan's expression I get sales for the entire year, want I'm looking for is sales of the previous year until the the max day available in my database
In my case the last available day is 01/06/2008
what I want to compare is sales of the ongoing year 2008 and sales from 1/1/2007 until 01/06/2008
Thanks
Tom
Hi,
Try this for previous year
For Previous Year:
Sum({$<OrderDate = {">=$(=Yearstart(Max(OrderDate), -1)) <= $(=AddYears(Max(OrderDate), -1))"}>} Sales)
Hope this helps you.
Regards,
Jagan.
that's not ot I'm afraid, this expresiion gives me sales of the entire previous year + ongoing year
What I'm looking for is the first 6 months of last year
Hi,
Try this expression
For Previous Year:
Sum({$<OrderDate = {">=$(=Yearstart(Max(OrderDate), -1))<=$(=AddMonths(Max(OrderDate), -12))"}>} Sales)
Regards,
Jagan.