Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Maybe try
=MonthName([All Inclusive Date])
as calculated dimension and
=Sum(If(MonthName([All Inclusive Date]) = MonthName([Group Date]), Amount))
as expression.
Maybe try
=MonthName([All Inclusive Date])
as calculated dimension and
=Sum(If(MonthName([All Inclusive Date]) = MonthName([Group Date]), Amount))
as expression.
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]))
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]
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] ) )
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!