Sum if Date For another field qualifies

[melissapluke]

All Inclusive Date	Date Type	ID
9/19/2017	Group	12345

Group Date	Amount	ID	Group ID
10/24/17	$500	12345	56234
9/19/17	$400	12345	86539

Hi all,

I have two tables, TableA with All Inclusive Date, Date Type, and ID in it. TableB with Group Date, Amount, ID and Group ID in it.

I am trying to create a table in the UI that includes the month of the all inclusive date, and the sum of the amount if the group date is in the same month as the all inclusive date. So in the case above, the sum would be $400. Any ideas on how to do this?

Thank you!

[swuehl · Accepted solution]

Maybe try

=MonthName([All Inclusive Date])

as calculated dimension and

=Sum(If(MonthName([All Inclusive Date]) = MonthName([Group Date]), Amount))

as expression.

[melissapluke]

I forgot to add in that I need to only sum if the DateType=Group, so I tried your expression with that included, but it did not work. I also tried Month and Date.

Sum({<DateType={'Group'}>}if(Month([All Inclusive Date])=Month([Group Date]),[Amount]))

[ssb]

Hi Melissa,

Load the tables data through Data manager.

If you use the derived date fields that is available in Date Fields section of the Fields Asset panel and in the expression editor , you can create this expression for the Table measure

=Sum(if([AllInclusiveDate.autoCalendar.Date]=([GroupDate.autoCalendar.Date]),Amount))

and the Table dimension

=[AllInclusiveDate.autoCalendar.Month]

[agigliotti]

what's wrong with the result you are getting using the below expression ?

Sum( {< DateType={'Group'} >} if( Month([All Inclusive Date]) = Month([Group Date]), [Amount] ) )

[melissapluke]

Having you question this made me look at my data load again. I realized that I wasn't loading the ID into the first table with "Distinct" and it was causing duplicate IDs and therefore the sum would double occasionally.  Thank you, it works now!