Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jenmclean
New Contributor II

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

Re: Show data from date range of previous year

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

8 Replies

Re: Show data from date range of previous year

If your selected date is myDate

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

datanibbler
Esteemed Contributor

Re: Show data from date range of previous year

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

Re: Show data from date range of previous year

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
New Contributor II

Re: Show data from date range of previous year

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

Re: Show data from date range of previous year

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
New Contributor II

Re: Show data from date range of previous year

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

Not applicable

Re: Show data from date range of previous year

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
New Contributor II

Re: Show data from date range of previous year

Thanks so much!

Jennie

Community Browser