Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

To show last 5 weeks data in pivot table

Hi All,

I have a requirement to show the last 5 weeks data in a pivot table. Can anyone help me in achieving this??

Thanks,

Leni Balakrishnan

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: To show last 5 weeks data in pivot table

you can use following expressiom

Since a week is fixed to 7 days, you can add them within a Date() function:

Date(Today() + (7 * vNumberOfWeeks), 'MMM-YYYY')

Final Expression>>

=sum({$<sale_date={>=$(=date(num(max(sale_date))-35)) <=$(=date(max(sale_date)))}>}sales)

Regards

Nitin

View solution in original post

7 Replies
Highlighted
Specialist II
Specialist II

Re: To show last 5 weeks data in pivot table

Please specify that you want data back from last five weeks or 35 days what is your week start day.

Highlighted
Creator III
Creator III

Re: To show last 5 weeks data in pivot table

last 5 weeks means from today back 5 weeks. Like, Last week, last before week & so on for 5 weeks data.

Highlighted
Not applicable

Re: To show last 5 weeks data in pivot table

Hi

You can use requiredDate>= date(today()-35,DateFormat)  in dimension.

If you are not using requiredDate column in dimesion then use this criteria in expression

 

{<requiredDate={">=$(=Date(today()-35))"}>}


Highlighted
MVP & Luminary
MVP & Luminary

Re: To show last 5 weeks data in pivot table

Something like sum({<MyDate = {'>$(=today()-35)'}>} Amount)


talk is cheap, supply exceeds demand
Highlighted
Specialist II
Specialist II

Re: To show last 5 weeks data in pivot table

you can use following expressiom

Since a week is fixed to 7 days, you can add them within a Date() function:

Date(Today() + (7 * vNumberOfWeeks), 'MMM-YYYY')

Final Expression>>

=sum({$<sale_date={>=$(=date(num(max(sale_date))-35)) <=$(=date(max(sale_date)))}>}sales)

Regards

Nitin

View solution in original post

Highlighted
Creator III
Creator III

Re: To show last 5 weeks data in pivot table

Thanks for your reply

I tried this option but data is not displaying in the chart.

I have Week as one of the dimension and if I use the above expression will it show only 5 weeks

1.jpg

I need to export this chart to excel and send as attachment in mail.

So whether we can restrict the chart to have latest 5 weeks data (as this chart is not visible to users and it is for only mail purposes) and trigger a mail on the scheduled time.

Highlighted
Creator III
Creator III

Re: To show last 5 weeks data in pivot table

In "activate sheet" trigger I have added the below expression for "select in field" trigger

Create 2 variables "From" & "To"

set From = num(Max(Date.Date))-35;

set To =  num(Max(Date.Date));

='>=' & Date(From, 'YYYY-MM-DD') & '<=' & Date(To, 'YYYY-MM-DD')

The Date format need to be changed based on your date field format.