Set analysis expression to calculate day over day, week over week using a date variable

[#1]

Hello,

I have 2 date variables vStartDate and vEndDate. I am trying to figure out  a set analysis expression to calculate day over day, week over week, month over month, year over year calculation.

i am using following expression to get data for vEndDate

sum({<Prod_type = {'aa'}, Date = {">=$(=timestamp(date#($(=chr(39) & vEndDate & chr(39)),'D-MMM-YYYY'),'M/D/YYYY hh:mm:ss TT'))<=$(=timestamp(date#($(=chr(39) & vEndDate & chr(39)),'D-MMM-YYYY'),'M/D/YYYY hh:mm:ss TT'))"}>}USD_VALUE)

With a timestamp conversion around dates, I am having difficulty coming up with an expression to subtract a day or add weekday logic.

Thank you so much.

[sunny_talwar]

Do you have a sample you can share? It would be good to know which object you are trying to do this in and what are your dimensions (if this is a chart)

[swuehl]

What is the format of your Date field? Is your Date field really formated 'M/D/YYYY hh:mm:ss TT'?

Dates in Set Analysis

Why don’t my dates work?‌

Get the Dates Right

[#4]

Hello, yes my date is in 'M/D/YYYY hh:mm:ss TT' format. On the other hand I was wondering if there is a way to declare/calculate variables in Qlikview's script editor so that I only load data for 5 days in the dashabord rather than full year, since anyway I will only be using current date, prev day, a week ago, a month ago, a year ago dates.

Thanks a bunch.

[swuehl]

Well, this seems like a different topic now, but in general, you can use a WHERE clause to limit the records you read in.

[#6]

Yes my intent is to limit the records via where clause, as in user enters one date in the Dashboard and qlikview script editor calculates previous dates accordingly and the Where clause would look like following:

Where Date in ('$currentDate','prevDay','7dayprev')

[sunny_talwar]

May be this:

Where Match(Num(DateField), Today(), Today() - 1, Today() - 7, AddMonths(Today(), -1), AddYears(Today(), -1));

[swuehl]

Create three variables in your script:

Let vCurrentDate = Today();

Let vPrevDay =Date(Today()-1);

Let v7DayPrevDay = Date(Today()-7);

LOAD

...

FROM ...

WHERE Match(Date,'$(vCurrentDate','$(vPreDay)','$(v7DayPrevDay)');

I think the format of your Date field should match the format of your variables, or try with an additional Num() in the variable definition.

[#9]

hi, I am suing 3 variables for Evaluation date (date entered by user) and calculating Prevday  and 7dayprev accordingly, the trouble is calculating the Prevmonth and Prevyear dates. Since in both these situations need to consider the previous business day if the day is in previous month happens to be on a weeked.

Regarding where clause I was referring to Where clause in SQL query not sure how can I use where clause in qlikview expressions.

[swuehl]

I am sorry, but I don't really like this kind of discussion, I think we will never get to an end if you always come up with a different request after we posted an answer.

I quit for now, probably Sunny can find your one-100 answers pretty fast.