Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Accepted Solutions
This really isn't a SET ANALYSIS problem. This can be handled easily in the LOAD SCRIPT using 2 awesome Qlik script function PREVIOUS and PEEK.
The PREVIOUS function does what it's name implies, it hands you the previous field value for the data you just read. Assume we have this is as our load script:
Data:
Load * Inline [
Year, Month, PartialValue
2020, 1, 100
2020, 2, 200
2020, 3, 300
2021, 1, 50
2021, 2, 100
2021, 3, 150
];
If we wanted to then create a new value called PreviousYearValue we could do something like this, note that I purposely sort the data in the order I want.
Join
Load
Year,
Month,
Previous(Year) as PreviousYearValue
resident Data
Order by Year, Month
That code would then yield the following table.
Notice that in the first result row we don't have a PreviousYearValue, because it's the first row. It's important to understand that because we need to handle that initial case. when we try to accumulate your values. Logically what we really want to do is say "If the Year you just read in is the same as the previous Year value then add my current PartialValue to the AccumulatedValue field, otherwise start my AccumulatedValue at the value of my PartialValue field."
If (Year = Previous(Year) , Previous(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue
Notice I said logically because we have a problem. While we READ in the Previous YEAR value, we never READ in the CummulativeValue it is IN MEMORY only it wasn't read. So while using Previous(Year) is fine for our IF condition, Previous(CummulativeValue) will yield an error message for us:
That's where the PEEK function comes in handy. It let's me "peek" into my In Memory data, and not just the actual data that was read.
Join
Load
Year,
Month,
If (Year = Previous(Year) , Peek(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue
resident Data
Order by Year, Month
Now we have what we need.
This really isn't a SET ANALYSIS problem. This can be handled easily in the LOAD SCRIPT using 2 awesome Qlik script function PREVIOUS and PEEK.
The PREVIOUS function does what it's name implies, it hands you the previous field value for the data you just read. Assume we have this is as our load script:
Data:
Load * Inline [
Year, Month, PartialValue
2020, 1, 100
2020, 2, 200
2020, 3, 300
2021, 1, 50
2021, 2, 100
2021, 3, 150
];
If we wanted to then create a new value called PreviousYearValue we could do something like this, note that I purposely sort the data in the order I want.
Join
Load
Year,
Month,
Previous(Year) as PreviousYearValue
resident Data
Order by Year, Month
That code would then yield the following table.
Notice that in the first result row we don't have a PreviousYearValue, because it's the first row. It's important to understand that because we need to handle that initial case. when we try to accumulate your values. Logically what we really want to do is say "If the Year you just read in is the same as the previous Year value then add my current PartialValue to the AccumulatedValue field, otherwise start my AccumulatedValue at the value of my PartialValue field."
If (Year = Previous(Year) , Previous(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue
Notice I said logically because we have a problem. While we READ in the Previous YEAR value, we never READ in the CummulativeValue it is IN MEMORY only it wasn't read. So while using Previous(Year) is fine for our IF condition, Previous(CummulativeValue) will yield an error message for us:
That's where the PEEK function comes in handy. It let's me "peek" into my In Memory data, and not just the actual data that was read.
Join
Load
Year,
Month,
If (Year = Previous(Year) , Peek(CummulativeValue) + PartialValue, PartialValue) as CummulativeValue
resident Data
Order by Year, Month
Now we have what we need.
