Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

leni_balakrishn
Contributor 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
gupta_n8
Valued Contributor 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

7 Replies
gupta_n8
Valued Contributor 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.

leni_balakrishn
Contributor 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.

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))"}>}


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
gupta_n8
Valued Contributor 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

leni_balakrishn
Contributor 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.

leni_balakrishn
Contributor 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.

Community Browser