Qlik Community

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
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)
1 Solution

Accepted Solutions
Highlighted

Re: SumIfs Using Two Dimensions

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)

View solution in original post

4 Replies
Highlighted

Re: SumIfs Using Two Dimensions

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)

View solution in original post

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

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
Partner

Re: SumIfs Using Two Dimensions

BAM! Thank you so much, that worked