Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks!
I have a question that is apparently simple because've seen several topics dealing with the subject, but none of the tips posted over there did not attend to my case.
The following is:
I have this table
I need a way to be shown only the values of the major dates in a pivot table
Name, date, value
A, 31/01/2016, 20
B, 17/01/2016, 10
c, 31/01/2016, 35
D, 31/01/2016, 20
The problem I'm having is that I can or that
Name, date, value
A, 31/01/2016, 20
B, 17/01/2016, 10
c, 31/01/2016, 35
D, 31/01/2016, 40
When use:
=sum({$<[FORMULARIO] = {'M*'},[COLUNA] = {'Em and*'},
[CLASSIFICAÇÃO] -= {'*Plantão*', '*Mutirão*', '*Júri*', '*Auxílio*'}, [date]={"=$(=max([date]))"} >} value)
or with this:
Name, date, value
A, 31/01/2016, 00
B, 17/01/2016, 10
C, 31/01/2016, 35
D, 31/01/2016, 20
When use:
=sum({$<[FORMULARIO] = {'M*'},[COLUNA] = {'Em and*'},
[CLASSIFICAÇÃO] -= {'*Plantão*', '*Mutirão*', '*Júri*', '*Auxílio*'}, [date]={"$(=max([date]))"} >} value)
The problem lies precisely in the names A and D.
Does anyone have any ideas?
Thanks in advance for suggestions.
Sunny T, we can!
I made a small change in your suggestion and it worked.
Join (Table)
LOAD Name,
Max (date) the date,
1 the MaxFlag
Resident Table
Group By Name, month;
Include the month in the group.
Thank you so much!
for date set analysis try with Date() function to match the format on LHS and RHS:
[date]={"$(=Date(Max([date]), 'DD/MM/YYYY'))"}
First, thanks for the help.
Unfortunately it does not. All numbers were at 0 (zero).
Had tried this syntax.
Improving my question, I have the following table:
Name, date, value
A, 31/01/2016, 20
A, 14/01/2016, 00
B, 28/01/2016, 10
B, 15/01/2016, 05
C, 12/01/2016, 10
C, 31/01/2016, 15
D, 31/01/2016, 20
D, 31/01/2016, 20
I would get this table as a result:
A, 31/01/2016, 20
B, 28/01/2016, 10
C, 31/01/2016, 15
D, 31/01/2016, 40
Still waiting for help.
Thanks again!
I suggest creating flag in the script for the max date of each month and then use that in your set analysis.
Script:
Table:
LOAD * Inline [
Name, date, value
A, 31/01/2016, 20
A, 14/01/2016, 00
B, 28/01/2016, 10
B, 15/01/2016, 05
C, 12/01/2016, 10
C, 31/01/2016, 15
D, 31/01/2016, 20
D, 31/01/2016, 20
];
Join(Table)
LOAD Name,
Max(date) as date,
1 as MaxFlag
Resident Table
Group By Name;
For it to be dynamic, you would need a front end solution. Try this:
=Sum(Aggr(NODISTINCT If(date = Max(Total <Name> date), value), Name, date))
Sunny T, we can!
I made a small change in your suggestion and it worked.
Join (Table)
LOAD Name,
Max (date) the date,
1 the MaxFlag
Resident Table
Group By Name, month;
Include the month in the group.
Thank you so much!
Awesome! I think I might have misread your requirement.
I am glad you were able to figure it out