Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to create a simple table which shows the user a total for a selected day and the total for the last 7 days. Ideally, I need to do this without any scripting or variables.
My table looks something like this:-
Ticket_Date | Ticket_Week_Start | Weight |
---|---|---|
1/1/17 | 1/1/17 | 10 |
2/1/17 | 1/1/17 | 12 |
3/1/17 | 1/1/17 | 16 |
4/1/17 | 1/1/17 | 12 |
5/1/17 | 1/1/17 | 10 |
5/1/17 | 1/1/17 | 12 |
9/1/17 | 8/1/17 | 15 |
9/1/17 | 8/1/17 | 10 |
The user selects a date from a list and it shows the total weight for that date. I also want a column that gives the last 7 days. I've tried using set analysis but don't seem to be doing it right.
The expression I'm using at the moment is:-
Code |
---|
SUM({ 1<Ticket_Date = {">$(=getfieldselections(Ticket_Date)) - 7, 'DD/MM/YYYY')"}> } Weight) |
This current returns: 0
Thanks in advance for any help!
Try,
SUM({ <Ticket_Date = {">$(=Date(Max(Ticket_Date) - 7, 'DD/MM/YYYY'))"}> } Weight)
or
SUM({ <Ticket_Date = {">$(=Date(Max(Ticket_Date) - 7, 'D/M/YY'))"}> } Weight)
Hi Carl,
this is another way of doing it, always last 7 day dynamic
sum({<Ticket_Date={"<=$(=max(date(addmonths(Ticket_Date, 0))))>=$(=max(date(addmonths(Ticket_Date, 0)-7)))"}>} Weight)