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