Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
uko223
Contributor III
Contributor III

Prior year set analysis based on date column

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?

 

DateActualPrior 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
1 Solution

Accepted Solutions
tincholiver
Creator III
Creator III

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)

 

Prior year.png

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

 

View solution in original post

3 Replies
tincholiver
Creator III
Creator III

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)

 

Prior year.png

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

 

View solution in original post

martenlouisse
Contributor
Contributor

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;

uko223
Contributor III
Contributor III
Author

Thank you!