SET Analysis to sum invoice amount for distinct invoice number

[SR2]

Hello Experts,

I have created following expression to get a count of invoices - 

COUNT(DISTINCT
{<
 [Payment Method] -= {'CreditCard'}
,[Invoice Type] = {'Standard'}
>}
IF ( ([ Invoice Hold Date]) <= EOMDate,
IF( ([Invoice Release Date]) >EOMDate,
[Invoice Number]))
)

Note - EOMDate is a end of month reporting date created for reporting. For this graph this is my dimension which has last 12 months of end of month dates.

I had to use DISTINCT otherwise it returns duplicate counts. I am unable to figure out how QS engine is counting duplicates (to the multiples of 30s) as my source view is not having these many duplicates. However, using distinct helped me here.

But now I am unable to do sum of Invoice amount for these invoices as again QS will count these duplicate rows.

Help needed - 

1) I saw there are posts which gives complex nesting of aggr, sum, distinct functions to solve similar issues. I am unable to figure out how to use in my expression as its already a combination of if statement with multiple conditions in set analysis. If you could help me achieve sum of invoice amount for distinct invoice numbers filtered based on above expr it will be huge help.

2) Any direction on how to identify root cause of QS engine counting duplicates would also help as I might be able to do fix in data load editor and avoid using distinct and the problem of summing will no more be a problem as well

Thank you in advance!

[agigliotti]

Hi @SR2 ,

I'm quite sure you have a problem with  the data model.
Could you share a sample qvw with mock data to look at?

[SR2]

Hi @agigliotti ,

Thank you for checking. 

I do not have QlikSense Desktop but using QlikSense in Web browser. I also do not have mock data. Is there a way to share qvf from web without data?

Thanks,

[agigliotti]

what's wrong with the below expression?

=sum(
{<
[Payment Method] -= {'CreditCard'}
,[Invoice Type] = {'Standard'}
>}
IF ( ([ Invoice Hold Date]) <= EOMDate,
IF( ([Invoice Release Date]) >EOMDate,
[Invoice Amount]))
)

[SR2]

Its summing up invoice amount multiple times due to duplicates 😞