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

Calculating LY Sales Metrics by Month or Date

Hello,

I am attempting to create a LY Sales metric so I can graph Sales and LY Sales by Month. My data source has sales by month, where the "Date" field contains the first day of the month (ex. 2/1/2014, 3/1/2014, 4,1/2014, ect)

I have added the following expression for this calculation, but can not get it to return values.

LY Sales = SUM({$<Date={$(=addyears(Date,-1))}>}Sales)

Can someone help me understand what I am doing incorrectly here? Or if there is a better way to approach this issue?

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Hi

This sort of analysis is much easier if you have a calendar that includes Month and Year as separate fields. During the LOAD...

     LOAD ...

          Year(Date) As Year,

          Month(Date) As Month,

          ....

For your chart, use Month as the dimension, and create two expressions:

  • Current year  =Sum({<Year = {"$(=Max(Year))"}>} Sales)
  • Previous year   =Sum({<Year = {"$(=Max(Year) - 1)"}>} Sales)

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Jonathan,

That is not getting me to my desired solution. I want to display the LY sales by month along side the sales for the current month as follows:

Ex. Jan-2016 sales are displayed and the LY sales column displays Jan-2015 sales

   

DateSalesLY Sales
1/1/2015130001000
2/1/2015140002000
3/1/2015150003000
4/1/2015160004000
5/1/2015170005000
6/1/2015180006000
7/1/2015190007000
8/1/2015200008000
9/1/2015210009000
10/1/20152200010000
11/1/20152300011000
12/1/20152400012000
1/1/20162500013000
2/1/20162600014000
3/1/20162700015000
4/1/20162800016000
5/1/20162900017000
6/1/20163000018000
7/1/20163100019000
8/1/20163200020000
9/1/20163300021000
10/1/20163400022000
11/1/20163500023000
Michael_Tarallo
Employee
Employee

Hi Courtney,

Thanks for your question.

Is the table you provided in the last post the actual source data, or is that your desired result?

Would a Pivot Table chart object do what you need, such as this:

As Jonathan mentioned you can lightly transform the data - using the data load editor - or by creating new dimensions in the master items list. If you create a Year and Month field from your date field it will make the task much easier.

LOAD

    "Date",

    Year(Date) as Year,

    Month(Date) as Month,

    Sales,

    "LY Sales"

FROM [lib://ThreadData]

(html, codepage is 1252, embedded labels, table is @1);

Also please check out these New to Qlik Sense Videos - id you have not to learn more about Qlik Sense.

See attached .qvf file - you can copy to your C:\Users\<user profile>\Documents\Qlik\Sense\Apps and refresh desktop with F5.

Note - the data sources is using this actual community thread and sucking in the data from the data table you provided.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik
Not applicable
Author

The data I provided was just demonstrative - not the actual source data. It does not contain "LY Amount" as a column. Ultimately what I am trying to calculate is a "Variance to Last Year" metric.

I have created a bar chart that displays by month and year, but my desired output is to have the bar in the chart represent the Variance from the current year to the previous year for that given month.

I have drawn in red boxes on the my current chart to indicate the metric that I would like to display.

Michael_Tarallo
Employee
Employee

OK great thanks - I sent this to a colleague of mine to see if what you want to do is possible. In the mean time, can you send some sample data / mock of data of your source so we may work with it. Or can we use the data you provided such as the date and sales column and just ignore the LY column?

Thanks,

Mike

Regards,
Mike Tarallo
Qlik
JonnyPoole
Employee
Employee

here is a possible quick solution

the LY field uses an expression that uses the above() function to use the row that is 12 places above.

the first 12 rows will always be blank with this technique, but i could work on an alternative to bring in previous values too.. would that be needed.

Capture.PNG