# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Partner

## SumIfs Using Two Dimensions

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?

Labels (2)

• ### sumifs

1 Solution

Accepted Solutions
Highlighted
MVP

## Re: SumIfs Using Two Dimensions

Create a link table like this

```Table:
*;
Current, Future, Sales
A, A, \$10
A, B, \$8
B, B, \$5
B, B, \$4
];

Key,
'Current' as Flag
Resident Table;

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)```
4 Replies
Highlighted
MVP

## Re: SumIfs Using Two Dimensions

Create a link table like this

```Table:
*;
Current, Future, Sales
A, A, \$10
A, B, \$8
B, B, \$5
B, B, \$4
];

Key,
'Current' as Flag
Resident Table;

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)```
Highlighted
Partner

## Re: SumIfs Using Two Dimensions

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

Highlighted
MVP

## Re: SumIfs Using Two Dimensions

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.

Highlighted
Partner

## Re: SumIfs Using Two Dimensions

BAM! Thank you so much, that worked