I'm currently trying to create a data item expression within Cognos Analytics report builder to aggregate some data. I'm a novice in building expressions in Cognos as generally I would export the data and work with it separately, but this report will be scheduled to run and needs to already be summarised within the list.
The relation I'm working with contains usernames, quantities and a date field which can be null. I want to sum quantities by username from tuples with non-null values for the date field but I am struggling with how this would be achieved within the normal aggregate([field1] for [field2]) structure?
I have tried both total([Quantity] for [Username] and [Date]) and total([Quantity] for [Username] and isnull([Date],0)) which both give errors.
Forgive me if I'm being stupid and thanks in advance for any help you can offer!
EDIT: As suggested by Rubrix, comma in place of "and" works (not sure why I didn't already try this) but I am also getting an aggregate for null values. Specifying the date to be within a range also doesn't work to get rid of the aggregated null values.
The expressions I've tried are:
total([Quantity] for [Sac Create User], [Stock Corr Date] between _first_of_month(_add_months(current_date, -1)) and _last_of_month(_add_months(current_date,-1)))
total([Quantity] for [Sac Create User], length([Stock Corr Date])>0)
Both still create an aggregate for nulls. If anyone has any suggestions on how to remove these without having to query separately and join it would be much appreciated!
Thanks
Try with "," instead of "and" or total([Quantity] for [Username], case when [Date] is null then 0 else [Date] end)