Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
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
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.
BAM! Thank you so much, that worked