Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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:
HTH
Jonathan
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 |
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
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.
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
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.