Sum by multiple criteria

[Duckabush]

Sample data is below. I need to sum by multiple fields, of which is dynamic data.

For example, I need to be able to get the total for a billing Period, which is by MonthYear. So Aug-2020, Sept-2020. But these need to be summed by File#, Customer, Vendor, and finally by MonthYear.

I can easily do it in Excel using SUMIFS, but not really sure how to do it in Qlik, and it is driving me nuts. I thought it would be simple. I have looked at many other examples, of code suggested by others, but I guess I just don't understand it clearly enough.

The last column in the attached file is my expected values after summing everything.

[avinashelite]

Try this expression :

Sum(TOTAL<File#,Vendor,MonthYear>Amount)

[Duckabush]

Doesn't seem to be working for me. Also getting out of calculation memory when I try to show more data. Not sure what is causing it to not work.

[MayilVahanan · Accepted solution]

Hi Duckabush,

Try like this

Sum(Total<File#, Customer, Vendor,MonthYear> Amount).

If your data is very huge, then rendering the chart takes sometime. Please give some condition to display the chart, so that its faster and you will not see the "Calculation Memory" issue.

Thanks & Regards,

Mayil Vahanan

[Duckabush]

@MayilVahanan This is what I had done, but still was not able to get it to actually do anything. Even with selecting a specific customer and file. It would still show the grand total for all months, next to each month.

This is how it ends up.

FEB-2020	$9242.00
APR-2020	$9242.00
SEPT-2020	$9242.00

This is the expected from the actual data.

FEB-2020	$178.00
APR-2020	$9064.00
SEPT-2020	$0.00

I think it may be my underlying code that is causing a problem. I think it is cause our File# is  are like 157.12231.1.1

The first is the department/location code, 1 Florida, 57 is IT, 12231 is just the next file to be generated for 157. the first .1 is the very second invoice of a file. 157.12231 would be the first. the second .1 would be the 2nd line of an invoice. such as We billed on the ".0" hours, the ".1" would be parts.

When I load in the additional IDs that break it down more, my numbers start to work correctly. However, it also breaks it down to 157.12231.1.1, and 157.12231.1.2, 157.12231.2.1, etc.

Honestly I have resolved the issue with the help of you. However I still don't know why my load statements were causing an issue.

The issue was with

Month(Date(Floor(INVOICEDATE), 'YYYY-MM-DD')) & '-' & Year(Date(Floor(INVOICEDATE), 'YYYY-MM-DD')) as MonthYear,

I changed it to

Date(monthstart(INVOICEDATE), 'MMM-YY') as MonthYear,

Along with

=SUM(TOTAL<File#,Customer,Vendor,MonthYear>Amount)