Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sales for ongoing year and compare with previous year

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

9 Replies
ramoncova06
Specialist III
Specialist III

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

ecolomer
Master II
Master II

See this examples

Not applicable
Author

sorry but it doesn't seem to work, if I use Sum({$<OrderDate = {">=$(=yearstart(Max(OrderDate)))  <= $(=(Max(OrderDate)))"}>} Sales


I get "-" as a result 

ramoncova06
Specialist III
Specialist III

I missed the closing ")" in my expression

Sum({$<OrderDate = {">=$(=yearstart(Max(OrderDate)))  <= $(=(Max(OrderDate)))"}>} Sales)


jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expression

For Previous Year:

Sum({$<OrderDate = {">=$(=Yearstart(Max(OrderDate), -1))<=$(=AddMonths(Max(OrderDate), -12))"}>} Sales)


Regards,

Jagan.