Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
SR2
Contributor III
Contributor III

SET Analysis to sum invoice amount for distinct invoice number

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!

 

4 Replies
agigliotti
Partner
Partner

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

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
Partner
Partner

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

Its summing up invoice amount multiple times due to duplicates 😞