topic Re: Sum if across tables in QlikView

Sum if across tables

ColdSpark — Fri, 22 Dec 2023 12:47:59 GMT

Hello, I’m relatively new to Qlik but have an app now with a table of raw data about 1000 records.
each record has a period_start_date field and an associated $ amount of value.

in a second table that I’ve created with the load editor, I have a date in each month going forward a year (dynamically created with addmonth).

I want to be able to add a column to my second table that returns the sum of the $ value for any record where it’s period_start_date falls within the month of the date in the second table.

assuming I need a join here? Would l

Re: Sum if across tables

vincent_ardiet_ — Fri, 22 Dec 2023 13:42:44 GMT

If you add a new column with end of month, you could maybe use intervalmatch?

Re: Sum if across tables

ColdSpark — Tue, 26 Dec 2023 10:03:53 GMT

Thanks Vincent, in this case though I want to essentially run what would be a SUMIFS in excel so I'm not sure interval match would work in this case? I've attached an excel file showing what I'd like to achieve to hopefully help folks understand where I'm at!

Re: Sum if across tables

vincent_ardiet_ — Thu, 28 Dec 2023 10:02:10 GMT

For me this should work, you add a period_end_date column in your raw data table like:
MonthEnd(period_start_date) as period_end_date

And then you can use interval match to join your table Months.

An other quick win solution could also be to just add a month field to your raw data table:
MonthStart(period_start_date) as Month

Re: Sum if across tables

Ahidhar — Thu, 28 Dec 2023 10:34:06 GMT

you don't need to use sumifs try this ,

Table1:
load date#(period_start_date,'DD-MM-YYYY') as period_start_date,Amount
Inline
[
period_start_date ,Amount
15-09-2023 , 21651465
16-09-2023 ,3212
10-10-2023 ,3321
30-10-2023 ,454566
11-11-2023 ,2546
13-11-2023 ,2246
14-11-2023 ,668863
];
NoConcatenate
Table2:
load Month,sum(Amount) as Result group by Month;
load
date(monthstart(period_start_date),'DD-MMM-YY') as Month,
Amount
resident Table1 ;