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

Current date Sales VS same date in previous year Sales

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

8 Replies
Not applicable
Author

Check out this demo, it has exactly what you want

Not applicable
Author

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
Not applicable
Author

Hello.

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

AddYears(Date, -1)

Regards.

javier_florian
Creator III
Creator III

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

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

Hi, Gloria.


Try something like this:


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