topic Re: Current date Sales VS same date in previous year Sales in QlikView

Current date Sales VS same date in previous year Sales

Tue, 17 Jun 2014 17:56:54 GMT

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

Re: Current date Sales VS same date in previous year Sales

Tue, 17 Jun 2014 18:15:01 GMT

Check out this demo, it has exactly what you want

Re: Current date Sales VS same date in previous year Sales

Tue, 17 Jun 2014 18:41:48 GMT

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

Re: Current date Sales VS same date in previous year Sales

Anonymous — Tue, 17 Jun 2014 18:45:43 GMT

Hello.

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

AddYears(Date, -1)

Regards.

Re: Current date Sales VS same date in previous year Sales

javier_florian — Tue, 17 Jun 2014 19:07:38 GMT

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

Re: Current date Sales VS same date in previous year Sales

Tue, 17 Jun 2014 19:10:49 GMT

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

Re: Current date Sales VS same date in previous year Sales

Anonymous — Wed, 18 Jun 2014 12:12:32 GMT

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

Re: Current date Sales VS same date in previous year Sales

Wed, 18 Jun 2014 16:23:29 GMT

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

Re: Current date Sales VS same date in previous year Sales

Anonymous — Wed, 18 Jun 2014 16:36:39 GMT

Hi, Gloria.

Try something like this:

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