Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jenmclean
Contributor III
Contributor III

Show data from date range of previous year

When someone enters in a specific date range from this year in an input box, I would like it to show gross revenue from the same dates of the previous year. My last year expression is as follows. How can I accomplish this?

So if I select 2/2/2014 through 2/15/2014, I want the corresponding dates from 2013 for Gross Revenue

({$<DB|Source={TP}, INV|VoidFlag-={Y}, TP|StoreNumber-={1,9}, Year={$(=max(Year)-1)}, INV|TransTypeID={1,17}>}TS|GrossRev)

1 Solution

Accepted Solutions
Not applicable

Sure, just needed to dig it up from one of my aps to make sure i give you the working one

Date={">=$(=Date(AddMonths(vStartDate,-12)))<=$(=Date(AddMonths(vEndDate,-12)))"}

Kind regards,

Lukasz

View solution in original post

8 Replies
alexandros17
Partner - Champion III
Partner - Champion III

If your selected date is myDate

use Addmonths(myDate,-12) to get the date an year before

datanibbler
Champion
Champion

Hi,

try replacing that Year_part in your set_expression with

>>> Year = {$(=(P(Year)-1))} <<<

=> Haven't tested it, but >>> P(Year) <<< in a set_expression usually means "whatever the user has selected in the year_field" - so if you take that and subtract 1, it should return the previous year.

HTH

Best regards,

DataNibbler

Not applicable

Hi Jennie, i would do it a bit differently:

it would require a button or action on text object for example.

Also two input box the startdate and enddate, after that you can prepare and variable which calculated a vsetperiod to "='>='&startdate&'<='&enddate" , then if you have a date field you could utilise the action "Select in Field" -> 

Field - Date

Search String - =vsetperiod

Or you could use the startdate and enddate directly in set analysis to make a pre selection on the dates from inputbox.

Kind regards,

Lukasz

jenmclean
Contributor III
Contributor III
Author

I have an input box and vStartDate and vEndDate variables already set. I select dates with this input box and I get the correct data in the TY column but does not change anything in the LY column. That's what I am trying to change

Not applicable

Ah yes i did not catch that you are using the current year and previous year columns.

Basically what the problem is with this functionality is that when you do the selection on the period, (as i presume you have in application only year and month visible, not the date field) it will not work for the Year-1 column as there are no common values. You have selected dates in the current year and want to select the previous year, for that i would use additional functionallity in the last year column.

AddMonths(vStartDate,-12) and AddMonths(vEndDate,-12) within the set analysis so instead of Year={$(=max(Year)-1)}

i would utilise Date =

As if you want to operate on the Date level, you can make selections on year and month (and still calculate correctly the last year), but it is not possible when you make a selection on date field.

jenmclean
Contributor III
Contributor III
Author

Can you give me an example? Am not sure how to script this.

Not applicable

Sure, just needed to dig it up from one of my aps to make sure i give you the working one

Date={">=$(=Date(AddMonths(vStartDate,-12)))<=$(=Date(AddMonths(vEndDate,-12)))"}

Kind regards,

Lukasz

jenmclean
Contributor III
Contributor III
Author

Thanks so much!

Jennie