Variable expansion is not working in Rangesum()

[Lokesh_5045]

Hi all, I am new to the qlik sense. I have created a variable i = 1 and used in the calculated dimension as below.

Let i = 1;
Let vI = 'I_M'&'$(i)'; // I_M1

If($(vI) < 30, RangeSum(       (Q_M$(i) / 30) * Mod(Round($(vI)),30),
If($(vI) < 60, RangeSum(        Q_M$(i) ,        (Q_M$(=i+1)/30) * Mod(Round($(vI)),30)),
If($(vI) < 90, RangeSum(        Q_M$(i),         Q_M$(=i+1),           (Q_M$(=i+2) / 30) * Mod(Round($(vI)),30)),'NA')))
as IT_Apr_2023

The above expansion should look like this;

If(I_M1 < 30, RangeSum(       (Q_M1 / 30) * Mod(Round(I_M1),30),
If(I_M1 < 60, RangeSum(        Q_M1 ,        (Q_M2 / 30) * Mod(Round(I_M1),30)),
If(I_M1 < 90, RangeSum(        Q_M1,         Q_M2,           (Q_M3 / 30) * Mod(Round(I_M1),30)),       'NA')))
as IT_Apr_2023

But it is giving error as below.

Q_M$(i) is expanding like Q_M1. But Q_M$(=i+1) is throwing error like 'Q_M' field is not found.

Please help me how can I increase the value of 'i' within the expression.

[Lokesh_5045]

@marcus_sommer I had the data in form of straight table. But all my formulae are based on months. So I changed the table to crosstable with Months as fields. Now in my formulae, I have to add the values associated to those months.

There are 4 fields in my straight table. Material, Fiscal_Month, Quantity, Norm_days

For example,
if ( norm_days associated with April < 120, sum of quantity (April, May, June) + July Quantity * mod(norm_days, 30) / 30)

if ( norm_days associated with May < 120, sum of quantity (May, June, July) + Aug Quantity * mod(norm_days, 30) / 30)

So to write those formulae, I converted Months as fields using generic load. Then I am using Rangesum() to add quantity values associated with those months.

[marcus_sommer]

Like above hinted it's solvable within normal data-structures with interrecord-functions within appropriate sorted resident-loads, for example you may apply: 

rangesum(peek('Quantity', -1), peek('Quantity', -2), peek('Quantity', -3)) as Rolling3Months

within a first load and within a second load with the months reversed sorted something like:

previous(Quantity)

to get all relevant information for the calculation on a single level. You may need here an there some further conditions to respect any related dimensionality and/or some more steps but in general it should work. You may just start step by step to comprehend the logic and data. If you have a larger dataset it would be helpful to extract a sub-set of it and developing the logic within an extra dummy-application. 

[Lokesh_5045]

@marcus_sommer @snibrahim1993 I am having the below data. I need to calculate Target field for those materials which have norm

Target field formula:
If (norm < 30, Quantity * mod(norm, 30) / 30,
if( norm < 60, Quantity + Quantity (add months, 1) * mod(norm, 30) / 30,
if( norm < 90, Quantity + Quantity (add months, 1) + Quantity (add months, 2) * mod(norm, 30) / 30,
if( norm < 120, Quantity + Quantity (add months, 1) + Quantity (add months, 2) + Quantity (add months, 3) * mod(norm, 30) / 30
.
.
if( norm < 180, Quantity + Quantity (add months, 1) + -----+ Quantity (add months, 4) + Quantity (add months, 5) * mod(norm, 30) / 30 )))))) as Target

The data looks like below.

Material	Month	Quantity	Norm	Target
A	Apr	8	56
A	May	4	28
A	Jun	7	157
A	Jul	-	89
A	Aug	4	67
A	Sep	-	176
A	Oct	3	-	NA
A	Nov	4	-	NA
A	Dec	2	-	NA
A	Jan	5	-	NA
A	Feb	4	-	NA
A	Mar	8	-	NA

I will explain one example. Lets take A and Month =  Jun
Norm = 157 = 5 * 30 + 7)

Target = Quantity of 5 months from June + Quantity of 6th month * mod(157, 30) / 30
Target = Quantity (Jun + Jul + Aug + Sep + Oct) + Quantity (Nov) * 7 / 30
Target = 7 + Null + 4 + Null + 3 + 4*7/30 = 14.93

[Lokesh_5045]

I haven't used set analysis much before. That's why I converted the data into cross table with Months as field names. So that I can add field by field using RangeSum(). Formula Script has to be written in editor.

[marcus_sommer]

I think the norm-query and their impact to the number of month which should be to consider within the accumulation could be included in the mentioned logic, with something like:

rangesum(Quantity', peek('Quantity', -1) * -(N<2), peek('Quantity', -2) * -(N<3), ...) as RollingNMonths

whereby N is something like: ceil(norm / 30) created one step before or maybe the logic is assigned to a variable.

[Lokesh_5045]

@marcus_sommer  @snibrahim1993 

Thank you. I understood that we can't increase Variable values while using in dimensions. We can only call dimensions associated with those variables.

So, I used 12 variables for 12 months and used them in my script as below.

I know this is not a great approach to solve and I don't have that much knowledge and experience to use above mentioned functions. I need time to learn all those things. Right now, this approach serves my purpose. Now I again convert this table to cross-table as you said and use them for front end visualizations.