Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am new to Qlik Sense. I have a data like below. I have to add number of selective fields as a calculated field. Please help me to write a script in backend.
Field1 | Field2 | Field3 | Field4 | fields to be added | Calculated field 1 | |
A | 3 | 6 | 6 | 7 | 2 | 9 |
B | 2 | 6 | 7 | 4 | 4 | 19 |
C | 1 | 8 | 2 | 5 | 3 | 11 |
D | 7 | 6 | 2 | 6 | 1 | 7 |
E | 7 | 1 | 9 | 10 | 4 | 27 |
Example A: Fields to be added - 2
Field1 (3) + Field2 (6) = 9
Example C: Fields to be added - 3
Field1 (1) + Field2 (8) + Field3 (2) = 11
I need this logic in backend script.
I think you could do it with RangeSum() and If().
RangeSum(
If([Fields to be added] >= 1, Field1)
,If([Fields to be added] >= 2, Field2)
,If([Fields to be added] >= 3, Field3)
,If([Fields to be added] >= 4, Field4)
) as [Calculated Field]
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Did you try below,
Field1 + Field2 as Total_2_field,
Field1 + Field2 + Field 3 as Total_3_field
From...
That would give me Total_1_field to Total_4_field (4 fields). It is not my calculated field.
My calculated field is based on numbers of fields to be added.
I guess, you have given me a hint. If "Fields to be added = 3", Then Total_3_field as my calculated field.
if(Field to be added = 1, Total_1_Field,
Field to be added = 2, Total_2_Field,
Field to be added = 3, Total_3_Field .....))) as Calculated_field
Let me try this approach.
In my case, there are month fields (total 12). I need to add values under month fields.
@Mr_Pearl Thank you for your advice. It worked. But in the above case, I have given an example of data 4 fields and one calculated field. To get 1 calculated field, we have created 4 total fields.
In my case, there are 12 month fields and each month field is having its own calculated field. In such case, we have to create 144 fields and this may not be a good idea.
Material | Jan | Feb | Mar | Apr | Months to be added | Calc_Jan | Calc_Feb | Calc_Mar | Calc_Apr |
A | 3 | 4 | 7 | 4 | 4 |
(Jan:Apr) 18 |
(Feb:May) |
(Mar:Jun) |
(Apr:Jul) NA (insufficient_data) |
B | 3 | 2 | 4 | 5 | 2 |
(Jan:Feb) |
(Feb:Mar) |
(Mar:Apr) |
(Apr:May) NA (insufficient_data) |
I think you could do it with RangeSum() and If().
RangeSum(
If([Fields to be added] >= 1, Field1)
,If([Fields to be added] >= 2, Field2)
,If([Fields to be added] >= 3, Field3)
,If([Fields to be added] >= 4, Field4)
) as [Calculated Field]
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
@rwunderlich That worked like a beauty. I was trying the combination of Rangesum() and for loop, but didn't get answer. I didn't get idea of combination of RangeSum() and If(). Thank you so much for making it very simple.