topic Re: Last 7 Days in QlikView

Last 7 Days

natisham — Tue, 24 Oct 2017 14:39:28 GMT

I have a script that shows hours and an EndDate column. I need to show a graph with dimension weekday (Mon,tues, wed, etc..) with an expression showing the hours of the last week I just uploaded.

I am unable to find an dynamic expression that will only show the hours for the days of the last week i uploaded. Need the expression to read the last week (EndDate) i uploaded without me selecting a date. Please help.

Re: Last 7 Days

alexandros17 — Tue, 24 Oct 2017 14:53:11 GMT

When you load data find the max value for EndDate then compute (into a variable 7 days before)

Use the variable in your dimension into the graph.

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 14:54:19 GMT

I cant figure out a correct variable to use. Any Suggestions?

Re: Last 7 Days

alexandros17 — Tue, 24 Oct 2017 15:03:43 GMT

For instance:

MyTab:

Select Max(EndDate) as maxi, Num(Max(EndDate)-7 as Maxi7 From ....

LET vMaxi7 = peek('Maxi7,0,'MyTab');

Then use Maxi7 in your Graph

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 15:38:13 GMT

Is there a way to have a calculated dimension?

=aggr($(maxi7), WeekDay)

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 15:39:22 GMT

Then my expression would be sum(hours)

Re: Last 7 Days

Anonymous — Tue, 24 Oct 2017 17:43:38 GMT

Assuming EndDate is a weekend date or something unique for each week.

sum({<EndDate={'$(=max(EndDate))'}>}Hours)

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 17:46:52 GMT

The expression only shows me sunday data. My dimension is Weekday. Any way to show the data for the whole week. Hours is uploaded daily named EndDate.

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 17:49:48 GMT

I am trying expression =if(EndDate >= (max(EndDate)-7) and EndDate < max(EndDate), Hours) but there is no data to display

Re: Last 7 Days

Anonymous — Tue, 24 Oct 2017 17:51:44 GMT

Easiest way is in the script, you need to add a column that is the same value for everyday in the week.

Something like

date(floor(WeekEnd(EndDate))) as WeekendDate

then you can do

sum({<WeekendDate={'$(=max(WeekendDate))'}>}Hours)

Re: Last 7 Days

natisham — Tue, 24 Oct 2017 17:57:32 GMT

Thank you! Worked like a charm!

Re: Last 7 Days

Brownie — Wed, 19 Oct 2022 07:46:02 GMT

What if i wanted to show one highest value of sales in last 30 days?

Re: Last 7 Days

Chip_Matejowsky — Wed, 19 Oct 2022 11:18:25 GMT

Hi @Brownie,

This thread is 5 years old, so suggest that you start a new thread to ask your quesitons.

Best Regards