Sum Columns matching and Sum Columns unmatching

Karineyumi — Wed, 13 Sep 2023 21:49:02 GMT

Hiiii

I have a dashboard where I have 2 different Databases:

Database 1 = Controlled Country

Database 2 = Country_Code

for my Dash i need to have a KPI that SUMs all the amounts where the info between datas are the same, and another formula that sums everything that doesnt match.

I was using the following formulas, but they are not working properly on the pivottable, it works only when i filter by an specific "booking"

sum(Aggr(if([Controlled Country]=Country_Code,SUM(Amount),0),[Master Customer Name]))

SUM(Aggr(if([Controlled Country]=Country_Code,0,SUM(Amount)), [Master Customer Name]))

Also, i need the formula to consider whatever is "null" on the Country_code field.

Appreciate your help! thankssss

Re: Sum Columns matching and Sum Columns unmatching

jwaligora — Thu, 14 Sep 2023 20:51:53 GMT

Hi.

When you say 'databases' do you mean 'tables'?

Are your tables currently linked by a common field? Sharing a snapshot of the 'table viewer' may be helpful here, as it will also expose your field names and possible primary keys.

If nulls need to be treated as data points for the purposes of your calculations, I would highly recommend using NullAsValue and Set NullValue...most likely applied to a table resulting from the join of your two data sets. This makes them more readily countable in set expressions.

topic Re: Sum Columns matching and Sum Columns unmatching in QlikView

Sum Columns matching and Sum Columns unmatching

Re: Sum Columns matching and Sum Columns unmatching