New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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?

Tags (2)
6 Replies
MVP

Re: Calculating LY Sales Metrics by Month or Date

Hi

This sort of analysis is much easier if you have a calendar that includes Month and Year as separate fields. During the 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

Not applicable

Re: Calculating LY Sales Metrics by Month or Date

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

 Date Sales LY Sales 1/1/2015 13000 1000 2/1/2015 14000 2000 3/1/2015 15000 3000 4/1/2015 16000 4000 5/1/2015 17000 5000 6/1/2015 18000 6000 7/1/2015 19000 7000 8/1/2015 20000 8000 9/1/2015 21000 9000 10/1/2015 22000 10000 11/1/2015 23000 11000 12/1/2015 24000 12000 1/1/2016 25000 13000 2/1/2016 26000 14000 3/1/2016 27000 15000 4/1/2016 28000 16000 5/1/2016 29000 17000 6/1/2016 30000 18000 7/1/2016 31000 19000 8/1/2016 32000 20000 9/1/2016 33000 21000 10/1/2016 34000 22000 11/1/2016 35000 23000
Employee

Re: Calculating LY Sales Metrics by Month or Date

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.

"Date",

Year(Date) as Year,

Month(Date) as Month,

Sales,

"LY Sales"

(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

Re: Calculating LY Sales Metrics by Month or Date

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.

Employee

Re: Calculating LY Sales Metrics by Month or Date

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
Employee

Re: Calculating LY Sales Metrics by Month or Date

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.