SumIfs Using Two Dimensions

[stschwarz]

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?

[sunny_talwar · Accepted solution]

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)

[stschwarz]

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

[sunny_talwar]

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.

[stschwarz]

BAM! Thank you so much, that worked