topic Re: Set analysis conditions between fields in App Development

Set analysis conditions between fields

Franco2 — Thu, 18 Nov 2021 03:54:50 GMT

Hi,

I'm trying to obtain the sum of a field but only those where another two fields are related. For example, my data set is something like this:

MonthA	MonthB	Amount
1	1	10
1	1	20
1	2	30
1	3	40
1	3	20
2	1	10
2	1	40
2	2	50
2	2	10
2	3	20

I want to get for every value of Month A the sum of Amount but only where MonthA=MonthB. For example for month 1 will be 30 and for month 2 will be 60. I'm trying to plot this values for all months. The dimension is Month A.

And also for more complex conditions like MonthA-4<MonthB<MonthA

I've tried

sum({<MonthA=P(MonthB)>} Amount)

but doesn't worked.

Re: Set analysis conditions between fields

abhijitnalekar — Thu, 18 Nov 2021 06:20:09 GMT

Hi @Franco2 ,

Are you looking for the below output?

you can achieve the same by adding new field at script level

if(MonthA=MonthB,Amount)as newAmount,

Re: Set analysis conditions between fields

tresB — Thu, 18 Nov 2021 06:22:44 GMT

You can simply try like:

Sum(If(MonthA=MonthB, Amount))

However, if you want set analysis, you have to have a row identifier from the data itself, like :

Load *, RecNo() As RowID from <>;

And then write set expression like:

Sum({<RowID={"=MonthA=MonthB"}>}Amount)

Re: Set analysis conditions between fields

Franco2 — Thu, 18 Nov 2021 15:50:16 GMT

I've tried it but when i want to use the new field doing a chart it is no usable.

If I write down sum( newAmount) it doesn't show anything.

Re: Set analysis conditions between fields

Franco2 — Thu, 18 Nov 2021 17:26:51 GMT

It worked, but now what if I want Month B to be higher than Month A-4.

Isn't it something like

Sum(If(MonthB=">=MonthA-4", Amount))

Sorry I'm kind of new to qlik sense.