Current date Sales VS same date in previous year Sales

[#1]

Hi there,

I am creating a chart showing the current dates (selected by users) sales and sales generating on same dates in previous year. We only have a column OrderDate in the format of mm/dd/yyyy, how can I use expressions to get it done? Thank you so much!

Best Regards,

Gloria

[#2]

Check out this demo, it has exactly what you want

[#3]

Hi Robert,

Thank you for your reply. The demo is helpful. But I am wondering how to use date or convert function to get the same date in previous year. Can you help me out with this one? Thank you so much!

Best Regards,

Gloria

[Anonymous]

Hello.

To convert a date to the same date in previous year, you can use:

AddYears(Date, -1)

Regards.

[javier_florian]

Hi Gloria,

When you a dimension like Sales and you have a date, you can build a expression like:

=Sum({1<YearMonth={$(vPrevYearMonth)}>} Sales)/1000000

Where vPrevYearMonth is a variable:

=Num(Year(Max(Date))-1)

-JFlorian

[#6]

Hi Brono,

Thank you so much for your reply. So can I use sum({<AddYears(OrderDate,-1)>} Sale/Rate) to calculate the sales in the previous year? I tried in qlikview, but it gave me error. Do you know what went wrong? Thank you!

Best Regards,

Gloria

[Anonymous]

Hello, Gloria.

What wet wrong is the Set Analysis syntax. You should use a expression like this (depending on how your date is formated):

sum({$<OrderDate = {$(=AddYears(OrderDate,-1))}>} Sale/Rate)

This will give you the sales of the same day last year. Notice that for it to work, you need to have only one OrderDate selected. Please remember that Set Analysis is evaluated only once per chart and not in a per row basis.

Regards

[#8]

Hi Bruno,

Thank you so much for your reply. I am wondering if there is a way flexible enough to show me the sales of the same period last year, which means no matter how many days my user select, it will show them the same period last year. Thank  you so much!

Best Regards,

Gloria

[Anonymous]

Hi, Gloria.

Try something like this:

Sum({$<OrderDate = {">='$(=AddYears(Min(OrderDate),-1))'<='$(=AddYears(Max(OrderDate),-1))'"}>} Sale/Rate)