topic Re: SumIfs Using Two Dimensions in QlikView

SumIfs Using Two Dimensions

stschwarz — Sat, 16 Nov 2024 05:15:20 GMT

I am trying to replicate a sumifs formula in Qlik Sense but am running into challenges. The business challenge is that there is a current attribute (account description) which has been updated (changed the mapping on some fields). I am trying to show a summary table that highlights the dollar variance that will occur due to the new mapping. Here is a example data set:

Current Future $

A A $10

A B $8

B B $5

B B $4

Summary Table:

Attribute Current $ Future $

A $18 $10

B $9 $17

I have tried using set analysis and a valuelist, but I am still running into issues. Does anyone know if this is possible?

Re: SumIfs Using Two Dimensions

sunny_talwar — Wed, 14 Nov 2018 13:36:56 GMT

Create a link table like this

Table:
LOAD RowNo() as Key,
	 *;
LOAD * INLINE [
    Current, Future, Sales
    A, A, $10
    A, B, $8
    B, B, $5
    B, B, $4
];

LinkTable:
LOAD Current as Attribute,
	 Key,
	 'Current' as Flag
Resident Table;

Concatenate (LinkTable)
LOAD Future as Attribute,
	  Key,
	 'Future' as Flag
Resident Table;

And then create a chart like this

Dimension

Attribute

Expressions

Current $
=Sum({<Flag = {'Current'}>}Sales)

Future $
=Sum({<Flag = {'Future'}>}Sales)

Re: SumIfs Using Two Dimensions

stschwarz — Wed, 14 Nov 2018 14:37:48 GMT

Thank you for the quick response. I am trying to think through this. Is this splitting the original data set into two data sets with the "key" field and creating a union? Or am I not thinking about that correctly

Re: SumIfs Using Two Dimensions

sunny_talwar — Wed, 14 Nov 2018 14:41:30 GMT

Yes, but this is done only for the two fields that you need under a single field name (Current and Future -> Attribute). Other fields in the fact table doesn't need to go into the link table. Only other field you need is a key field which will be the link between your fact table and the link table.

Re: SumIfs Using Two Dimensions

stschwarz — Wed, 14 Nov 2018 15:45:21 GMT

BAM! Thank you so much, that worked