Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
9 Replies
ramoncova06
Valued Contributor III

Re: Sales for ongoing year and compare with previous year

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
Honored Contributor II

Re: Sales for ongoing year and compare with previous year

See this examples

Not applicable

Re: Sales for ongoing year and compare with previous year

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
Valued Contributor III

Re: Sales for ongoing year and compare with previous year

I missed the closing ")" in my expression

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


MVP
MVP

Re: Sales for ongoing year and compare with previous year

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

Re: Sales for ongoing year and compare with previous year

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

MVP
MVP

Re: Sales for ongoing year and compare with previous year

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

Re: Sales for ongoing year and compare with previous year

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

MVP
MVP

Re: Sales for ongoing year and compare with previous year

Hi,

Try this expression

For Previous Year:

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


Regards,

Jagan.

Community Browser