Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My sample data/table looks like such:
Month | Company | Promise | Actual |
Jan-15 | ABC | $10 | |
Feb-15 | ABC | $20 | |
Mar-15 | ABC | $30 | |
Apr-15 | ABC | $40 | |
May-15 | ABC | $50 | |
Jun-15 | ABC | $60 | |
Jul-15 | ABC | $70 | |
Aug-15 | ABC | $50 | $80 |
Sep-15 | ABC | $0 | |
Oct-15 | ABC | $0 | |
Nov-15 | ABC | $0 | |
Dec-15 | ABC | $0 | |
Jan-16 | XYZ | $10 | |
Feb-16 | XYZ | $20 | |
Mar-16 | XYZ | $30 | |
Apr-16 | XYZ | $40 | |
May-16 | XYZ | $50 | |
Jun-16 | XYZ | $40 | $60 |
Jul-16 | XYZ | $70 | |
Aug-16 | XYZ | $80 | |
Sep-16 | XYZ | $0 | |
Oct-16 | XYZ | $0 | |
Nov-16 | XYZ | $0 | |
Dec-16 | XYZ | $0 |
I would like to calculate the the sum of the amount for the past 6 months from the date of the promise for each company.
So for Company 'ABC', I would like to show $300 and for Company 'XYZ', I would like to show $210.. either by adding a new column or performing a set analysis in a chart. I've spent a few hours trying to figure this out and i haven't gotten far.
Thanks guys.
Regards,
Vel
Hi,
Looking at the data it seems for Company 'ABC' the total should be $330 instead of $300
(
15-Mar | ABC | $30 | |
15-Apr | ABC | $40 | |
15-May | ABC | $50 | |
15-Jun | ABC | $60 | |
15-Jul | ABC | $70 | |
15-Aug | ABC | $50 | $80 |
)
CORRECT?
LET vDate = '20-01-2016'
LET vParticulatrDate = Date(monthstart(Makedate('$(vDate)',1),-6))
Here, Date should be your fixed date.
Sum({<Comapny = {'ABC'}, Date = {'$(vParticulatrDate)}>} Amount)
Correct! my error...
Thanks... this helps somewhat. However, I will need vDate to be dynamic as each company would have a different vDate.
Maybe Something like this
if(Len(Promise)>0 ,RangeSum(Above(SUm(Actual),0,6)))
I would like for my output to be:
Company | Promise | Actual (6 months) |
ABC | $50 | $330 |
XYZ | $40 | $210 |
I like this but I will need to use date as there could be more than 2 entries for in a month and I need to capture those as well.
Something like this
Sum({<Month={">date(AddMonths(Month1,-6),'MMM-YY') <=date(AddMonths(Month1,0),'MMM-YY')"}>} Actual)
Where Month1 is
Date(If(Len(Promise)>0,Month),'MMM-YY') as Month1