Measure of Last Year's sales with this year's dates?

[BenB1]

Having a severe problem with date sorting per my last question (https://community.qlik.com/t5/Qlik-Sense-App-Development/Sorting-of-current-last-year-sales-data-ove...

Is there a way I can simply make a measure that has all of last year's sales, but the dates are this year's? Even just the year/month needs to be the same not day to day. I need to overlay this and last year's sales on the same chart, but it's not working since December shows up at the end when it's really december of 2019 and should come before december 2020. I can't use Year Month as the dimension, because then it's spread over 24 months instead of 12 and they're no longer side by side in the chart.

[QFabian]

Hi @BenB1 the link that you wrote is no working, but,  as you describe your problem, i suggest you make some script, and not a expression solution.

If you are going to use last year dates data often, you can add a field for example  'LY Sales', and is going to be next your normal 'Sales' Field.

Yo can try something like this:

Data:

fields,

key fields....,

DateField,

Sales

From yoursource ;

left join

Load

key fields....,

addmonths(DateField,12)    as DateField ,

Sales  as [LY Sales]

Resident Data;

[BenB1]

Thought about this, but the data is currently one row per sale and totaled up. Not sure I'd be able to add a custom field that totals up correctly that way.

But since these charts are meant for month by month I can probably just make a view with month totals and a same month last year total which should let me sort them out on the same chart, I'll try that and see how it comes out

[QFabian]

Ok, so if you can create a chart, can be a line, with one dimension, MONTH, and two expressions , one of them with 'set analysis' :

Last Year : sum(Year={$(=max(Year)-1)}  Sales)

Current Year : sum(Sales)

so if the user clics on one year, it will show tw lines with months and their sales amount

[edwin]

further to the first solution, create a bridge that relates you current date and prior year date. 

NoConcatenate
calendar:
load date(Today()-RowNo()) as Date;
load 1 as num AutoGenerate (900);

NoConcatenate
Bridge:
load Date, Date as FactDate, 'CURRENT' as DateType Resident calendar;

concatenate(Bridge)
load Date, addyears(Date,-1) as FactDate, 'PRIORYEAR' as DateType Resident calendar;

in attached when you select PRIORYEAR, your date is associated with the day one year ago.  so in you r chart you use date as dimension, 1st expression is sum({<DateType={'CURRENT'}>}someMeasure) as the current year total; 2nd expression is sum({<DateType={'PRIORYEAR'}>}someMeasure) as the prior year total

[BenB1 · Accepted solution]

I ended up making a separate measure for a YearMonth that would always show the last 12 months

=

date(
if( ([TicketDate.autoCalendar.YearMonth]>= AddMonths(Today(),-12,'YYYY-MM')),
Date([TicketDate.autoCalendar.YearMonth],'YYYY-MM'),
if ([TicketDate.autoCalendar.YearMonth]<= AddMonths(Today(),-24,'YYYY-MM')
//For the last bit of the current
,Date(AddMonths([TicketDate.autoCalendar.YearMonth],24),'YYYY-MM')
,Date(AddMonths([TicketDate.autoCalendar.YearMonth],12),'YYYY-MM')
)
)
, 'MMM-YYYY')

It's ugly without the formatting, but basically what it does is produces a YearMonth value that only shows the last 12 month's worth of dates. This way I can add two measures on the same chart, one for last year one for this year, and they're overlaid properly, AND the sorting order is proper instead of jan-dec.

The second conditional is because it's halfway through the month, so some of the data is actually from 2018 to fill out the rest of current November. For that one bit, it's bumped up 2 years.