How to derive measure only the specific month that falls under range.

Raya — Tue, 15 Oct 2024 14:02:58 GMT

hello Qlik experts,

I looking for the solution to implement below in qlik. I have Report date and change date, total_value fields in my DM.

I need derive 10 months based on report month and total value will be divided and show only those months those falls between Change month and Report month.

would some one please help me on this..?

Change_ Date

Report_Date

Total_value

Sep-24

Oct-24

Nov-24

Dec-24

Jan-25

Feb-25

Mar-25

Apr-25

May-25

Jun-25

31-Dec-2024

01-Sep-2024

135402.96

33850.74

Re: How to derive measure only the specific month that falls under range.

Qrishna — Wed, 16 Oct 2024 02:13:37 GMT

Hi - i wouldnt prefer this particular view but if your requiremnt is to show the one like above:

Try the below code in your script, its a bit lengthy though:

//Load original Data
Data:
Load Rowno() as Key, *;
LOAD * INLINE [
Change_Date, Report_Date, Total_Value
31-Dec-2024, 01-Sep-2024, 135402.96
];

// Fetch the Min/max of the DateRange
// Although this step is not needed, you directly do the 'Peek' step
Date_Temp:
//LOAD Date(MonthStart(Date#(Change_Date, 'DD-MMM-YYYY')), 'DD-MMM-YYYY') as Month_Start_Date
//RESIDENT Data;
LOAD Date(MonthStart(Date#(Report_Date, 'DD-MMM-YYYY')), 'DD-MMM-YYYY') as Month_Start_Date
RESIDENT Data;

MinMax:
Load Min(Month_Start_Date) as MinDate,
Max(Month_Start_Date) as MaxDate
Resident Date_Temp;

Let vMinDate = Peek('MinDate',0,'Min_Max');
// Let vMinDateMonth =Month($(vMinDate));
// Let vMinDateYear =Year($(vMinDate));

//Generate 10 months and join it the 'Data' Table
left join(Data)
ten_Months:
Load MakeDate, Month(MakeDate) & '-' & Year(MakeDate) as Month_Year;
Load
Date(AddMonths($(vMinDate), IterNo()-1), 'YYYY-MM-DD') as MakeDate
AutoGenerate 1
While IterNo() <= 10;
drop tables Date_Temp, MinMax;

//Now find the Months that needs to be populated with divided Total_value
Data_Temp:
Load *,
If(Date#(MakeDate, 'YYYY-MM-DD') >= Date#(Report_Date,'DD-MMM-YYYY') and
Date#(MakeDate, 'YYYY-MM-DD') <= Date#(Change_Date,'DD-MMM-YYYY'), 1, 0) as InRange
Resident Data;
drop table Data;

// Create a table to count the total number of months in range
// you cannot directly use Sum() or aggregation functions in a Let statement because Qlik executes Let statements at script runtime,
// whereas Sum() is evaluated in the data model (chart-level or table-level functions).
// To achieve the desired result, you need to use Peek() to retrieve the result from the table after the calculation.
TempSum:
LOAD
Sum(InRange) as NumMonthsInRange
RESIDENT Data_Temp;

// Peek the calculated value from the table into a variable
Let vNumMonthsInRange = Peek('NumMonthsInRange', 0, 'TempSum');
DROP TABLE TempSum;

// Load the final data, distributing Total_Value across the months
Split_Data:
LOAD Key,
Month_Year,
If(InRange = 1, Total_Value / $(vNumMonthsInRange), 0) as Div_Value
RESIDENT Data_Temp; // Generate one record for each month in range
drop fields Month_Year, MakeDate, InRange from Data_Temp;

//Now load the distinct row, as ts generated duplicated rows column
NoConcatenate
Data:
Load Distinct *
Resident Data_Temp;
drop table Data_Temp;

//Prepare the final view table

//Let vFieldName = Peek('Month_Year',0,'Split_Data');
//Let vFieldValue = Peek('Div_Value',0,'Split_Data');
//
//join(Data)
//Load distinct Key,
// $(vFieldValue) as "$(vFieldName)"
//Resident Split_Data;

for i = 0 to NoOfRows('Split_Data') - 1

// Get the Month_Year field name and Div_Value for the current row
Let vFieldName = Peek('Month_Year', i, 'Split_Data');
Let vFieldValue = Peek('Div_Value', i, 'Split_Data');

// Join to the Data table
JOIN (Data) // Make sure to join to the Data table
LOAD DISTINCT
Key, // Keep the existing Key to join on
$(vFieldValue) as [$(vFieldName)] // Create a new field with the dynamic name
RESIDENT Split_Data;

next;

Drop table Split_Data;

topic Re: How to derive measure only the specific month that falls under range. in App Development

How to derive measure only the specific month that falls under range.

Re: How to derive measure only the specific month that falls under range.