Variable in Load with Set Analysis expression or be calculated in LOAD and stored in a Field

edupitel7 — Sat, 16 Nov 2024 18:18:55 GMT

Hi, I doubt how to do this in qlik sense:

My Example Table:

Area | Sales

A 500

B 1000

C 100

I want to create a measure to calculate this:

if(Area='A', sum({<Area={"A"}>} Sales)+sum({<Area={"B"}>} Sales),sum(Sales))

In resume, it calculates the value of Sum for A and B when my Area is equal A
I know how to do something related to it but using the above function

I want to understand how could I manage to do this because any moment that I use
If (Field = "A", ...)

I can't use any other field values like B or C, it is like a "filter" that limits my calculations for only values in rows that field is equal A.

In google sheets or excel I can work on each cell per time

In cell C2 I'd write => sumifs(B:B;A:A;"A")+ sumifs(B:B;A:A;"B") or any other variation

In cell C3 and C4 I can use completely independent formulas of C2

In qlik I only can setup the whole column measure value

Yes, I can use pick(match())

but again if "matches" it also filters

if(Area='A', sum({<Area={"A"}>} Sales)+sum({<Area={"B"}>} Sales),sum(Sales))

Doesn't work

I've tried to create a variable and call the variable, it doesn't work, it calculated the sum(B Sales)=0

What I need is a way of calculating in LOAD and store this variable in a Field

like that :

Let vExp2 = 'if(Area='&chr(39)& 'Assembly' &chr(39)&',sum({<Area={"Assembly"}>}Act)+sum({<Area={"Electronics"}>} PP),sum(Sales))';

ALOS:
Load
'$(vExp2)' as vALOS
AutoGenerate 1;

But in my table, it writes the whole expression instead of evaluating it and show the value.

The output that I want

Area | Sales | meausure

A 500 1500

B 1000 1000

C 100 100

Yes, I can do in this simple case using the above

But I want to know how to manage this situation in more complexes cases

Are there other ways to manage this, without using the above function?

I think it is and challenge

I wasted more than 20 hours waiting for a solution and nothing worked properly.

topic Variable in Load with Set Analysis expression or be calculated in LOAD and stored in a Field in App Development