Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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,
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)
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.
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.