How to use Peek() with grouping of few fields

[qliknewbie_admin]

Hi,

I have below data to work on to get total demand and supply for every year. 

The calculation for each year will be based on previous year. Then, within it as well to get demand there is a formula to apply with the same rule. 

I know for this, we need to use Peek() function..however, i am not able to come out with it if need to do grouping,

Attached is the file together with the formula.

Formula:

The calculation will be group by Position to get total demand and supply for each year.

Notes: (CY)= CurrentYear, (PY)=PreviousYear

1. Filled position (CY)= Total demand (PY)

2. Retain = Filled position (CY)- Retire TBF (CY) - Retire NTBF (CY) - Retire Delimit (CY)

3. Vacant (Previous Year) = Retire NTBF (CY) + Vacant NTBF (CY)

4. Total Demand =Filled position (CY) + Retire TBF (CY) + Vacant TBF

5. Total Supply = Retain (CY)

Thanks for any help!

[morgankejerhag]

Is columns A-D input data that you don't have to calculate in Qlik? Do you want a solution that is calculated in the script or is it ok to calculate in measures in charts and tables? How do you want the final report to work? Should it be shown as a table with position and year + measures, or should it be possible to aggregate for example Retain for all positions? All years?

[morgankejerhag]

You can solve it in the script like

// Get data from source and fix a Key

DataIn:

Load

Cal_Position_Text,

    Cal_FinancialYear,

    Autonumber(Cal_Position_Text & '|' & Cal_FinancialYear) as Key

FROM [lib://Morgan Qlik Community:DataFiles/Calculator table.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Generic loads pivots the table and the rows becomes columns in seperate tables

GenericData:

generic

LOAD

    Autonumber(Cal_Position_Text & '|' & Cal_FinancialYear) as Key,

    CalCat,

    FinalCountStatus

FROM [lib://Morgan Qlik Community:DataFiles/Calculator table.xlsx]

(ooxml, embedded labels, table is Sheet1);

// Loop through the tables and join them to a combined table

Set vListOfTables = ;

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='GenericData' Then

Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

End If

Next vTableNo

DataJoined:

Load distinct Key resident DataIn;

For each vTableName in $(vListOfTables)

Left Join (DataJoined) Load * Resident [$(vTableName)];

Drop Table [$(vTableName)];

Next vTableName

left join (DataJoined) Load * resident DataIn;

drop table DataIn;

// Calculate the new columns

Data:

Load

*,

    [Filled Position (Previous Year)]- [Retire TBF] - [RetireNTBF] - [Retire Delimit] as Retain

    // Add more calculated columns here

resident DataJoined;

drop table DataJoined;

[qliknewbie_admin]

Hi Morgan,

Thanks for your help!

It works for 2025, however, how do i use Peek() for the rest of the year using the previous year data?

[morgankejerhag]

Do you mean to get Total Demand (calculation 4) for 2025 into Filled position for 2026?

1. Filled position (CY)= Total demand (PY)

[qliknewbie_admin]

Hi Morgan,

Many thanks for your help.. I really appreciate it..

For the calculation..we need to calculate for 2025 first..then using calculated data for 2025, we need to calculate for 2026. 

For example..we need to calculate Total Demand for 2025, then we will need to use Total Demand for 2025 as a Filled position for 2026..

This is where I still stuck..hope you can help me 🙂

Thanks in advaced :):)