Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please specify that you want data back from last five weeks or 35 days what is your week start day.
last 5 weeks means from today back 5 weeks. Like, Last week, last before week & so on for 5 weeks data.
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))"}>}
Something like sum({<MyDate = {'>$(=today()-35)'}>} Amount)
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
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
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.
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.