Distinct clause in qliksense

[Sheetal]

Hello all

I have a table which has the following fields

Assistance ID Hours YearMonth
1439984 2 2018Jan
1439984 2 2018Jan
1439984 2 2018Jan
1439986 3 2018Mar
1439986 3 2018Mar

This is already grouped by Assistance id. I have a qlik straight table with YearMonth as a dimension and Sum(Hours) as one of the mesaures.I want to count the hours only once if the assistance id is multiple times .I suppose I could use the distinct clause but not sure how to link it to the assistance id. For example for the month of Jan2018, the sum(hours) = 2 and not 6.
Is there a way to do this using an expression. Thanks in advance.

Sheetal

[Claudiu_Anghelescu · Accepted solution]

=Sum(Aggr(Only(Hours),[Assistance ID], YearMonth, Hours))

[Akshesh_Patel · Accepted solution]

hi,

=Sum(distinct(Hours))

- See the attached image

- Please mark the thread as a solution, if any solution from this post has been worked for you. 

Thank you

Akki

[Sheetal]

Thank you.