Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am striking out on this set analysis....
I have the following columns in my data set:
CHG AMT: Amount that was charged for the item
CHG Date: The date the item was charged
CHG Period: It is a Year/Month combination using our BILLING Calendar. So the 1st of every month belongs to the month before.
Ex. CHG DATE= March 1, 2012
CHG Period = 201202
BUT
CHG DATE= March 2, 2012
CHG Period = 201203
I am trying to get a constant that represents the total amount billed (CHG AMT), for the most recent [CHG Period] based off the recent date in the file (Max(CHG DATE))
Sample Data:
CHG AMT CHG Date CHG Period
15.50 2/29/2012 201202
20.75 3/01/2012 201202
10.25 3/01/2012 201202
5.50 3/02/2012 201203
8.00 3/03/2012 201203
10.65 3/03/2012 201203
Max(CHG DATE)=3/3/2012
Which is Period--> 201203
Sum of Charges for all of 201203: Expected Answer $24.15
Any Advice?
=sum({<[CHG Period]={"$(=max([CHG Period]))"}>}[CHG AMT])
This should return the sum of CHG AMT for the maximum CHG Date:
sum({<[CHG Date]={'$(=max([CHG Date]))'}>} [CHG AMT])
I seem to be getting 0 for an answer...
I added a sample to my original post that contains the simple data set from my sample data.
Your dates have a different format than the document default. Try formatting the date:
=sum({<[CHG Date]={"$(=date(max([CHG Date]),'M/DD/YYYY'))"}>}[CHG AMT])
That fixes the formatting issue but the total CHG AMT comes out to $18.65. It is only summing the Max(CHG Date). What I am looking for is the total CHG Period that the Max(CHG Date) is in.
So if 3/3/2012 is the max date.... that is in CHG Period 201203
so the answer would be the sum of all dates in the 201203 CHG Period, which equals $24.15
Thank you for your help, I really appreciate it.
=sum({<[CHG Period]={"$(=max([CHG Period]))"}>}[CHG AMT])
Thank you so much! That worked like a charm. I think I was over complicating it for no reason.
Thanks Again!
Hi Gysbert,
Can I ask you one more question....
Do you know why the 1 is not working to ignore the selection?
=NUM(sum({1<[CHG Period]={"$(=(max([CHG Period])))"}>}CHG_AMT),'$###,000.00' )
Thanks
max([CHG Period]) still takes selections into account. If you want this to always be the absolute maximum then change it to max({1}[CHG Period])
I just learned something new! I did not know you could put the {1} there. All the examples I was looking at showed it in the beginning before the <>.
Thank you for all your help, and your SUPER fast reply!