topic Re: Prior year set analysis based on date column in QlikView

Prior year set analysis based on date column

uko223 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a table that I need current year amount and prior year amount on. I have a date column to drive the measures. The first measure is fine as the date column drives the Actual amount. However, for prior year, I need the amount to be driven on the date column minus 365. So 2/26/2019 - 365, 3/1/2019 - 365, etc. How can I accomplish this?

Date	Actual	Prior Year
2/26/2019	$20,025	$0
3/1/2019	$23,816	$0
2/25/2019	$13,246	$0
2/27/2019	$14,021	$0
2/28/2019	$23,697	$0
3/2/2019	$126	$0
2/24/2019	$6,247	$0

Re: Prior year set analysis based on date column

tincholiver — Mon, 04 Mar 2019 15:45:11 GMT

if you put as dimensions month, day and year in a pivot table will give as a result which were the sales for a given day of the year and the respective day of the previous year. if you want to use set analysis you would have to make two expressions one for the current year and one for the previous year:
sum ({<Year = {'> = $ (= max (Year))'}>} Sales)
sum ({<Year = {'<= $ (= max (Year) -1)'}>} Sales)

I do not know if it's the result you're looking for. tell me if that solution serves you

Re: Prior year set analysis based on date column

martenlouisse — Mon, 04 Mar 2019 15:49:53 GMT

You could it arrange it your script with something like this:

Table:
LOAD
Date,
Actual
Resident YourTable;

Concatenate

LOAD
Date + 365 as Date,
Actual as [Prior Year]
Resident YourTable
Where Date <= Date(Today())-365;

Re: Prior year set analysis based on date column

uko223 — Wed, 06 Mar 2019 13:44:55 GMT

Thank you!