Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
melissapluke
New Contributor III

Sum if Date For another field qualifies

All Inclusive DateDate TypeID
9/19/2017Group12345

Group DateAmountIDGroup ID
10/24/17$50012345

56234

9/19/17$4001234586539

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum if Date For another field qualifies

Maybe try

=MonthName([All Inclusive Date])

as calculated dimension and

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


as expression.

5 Replies
MVP
MVP

Re: Sum if Date For another field qualifies

Maybe try

=MonthName([All Inclusive Date])

as calculated dimension and

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


as expression.

melissapluke
New Contributor III

Re: Sum if Date For another field qualifies

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
New Contributor III

Re: Sum if Date For another field qualifies

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
Honored Contributor II

Re: Sum if Date For another field qualifies

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
New Contributor III

Re: Sum if Date For another field qualifies

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!