Curve: LOAD Flight, DaystoDeparture, DepDate, Sum(Sold) as Total_Booking, numsum( Sold, peek( 'Bsum' ) ) as Bsum RESIDENT Transactions GROUP by Flight, DepDate, DaystoDeparture ORDER by Flight, DepDate desc, DaystoDeparture desc
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
In the QV Help there is the function numsum with the example:
Load A, B, numsum( B, peek( 'Bsum' ) ) as Bsum...;
creates an accumulation of B in Bsum.
This is what I am looking to do. It looks like I can use the 'Bsum' field in an expression even though it is created in the load statement, and not from the underlying data source.
I think the problem is related to the fact that you are trying to accumulate values in the same load statement that's also aggregating using "Group BY".
The "Group By" syntax is quite demanding - you can only list key fields and aggregated functions there (hence the syntax error).
I think you need to aggregate the data first, and then reload it again using "Resident" load, to calculate your accumulated total.
A few things raise questions:
- what are you trying to do with sum(Previous(Sold)) ? I'm not sure what kind of results you might get from this function, in combination with the Group BY...
- What numbers are you trying to summarize under Total_Curve?
- Do you want your total to accumulate data across all Flights, or keep separate totals for different Flights?
Bottom line - separate accumulation and aggregation into 2 different statements...
Function numsum has been replaced with the function RangeSum(). The rest of your assumptions are correct - you can accumulate values this way. You might want to order your data in a certain way, and if you have any other key fields (Item, or Customer, etc...) - you might need to check if the previous(key) equals to the current value of the Key.
I changed it to the following and it Loads without error, but my results are not what i expected.
Curve: LOAD Flight, DaystoDeparture, DepDate, Sum(Sold) as Total_Booking, sum(Previous(Sold)) as Prev_Sold, only(rangesum(Sold, Peek('Total_Curve'))) as Total_Curve // numsum( Sold, peek( 'Bsum' ) ) as Bsum RESIDENT Transactions GROUP by Flight, DepDate, DaystoDeparture ORDER by Flight, DepDate desc, DaystoDeparture desc
I added only() because it was giving me a syntax error, So i assumed that rangesum() was the issue. when I look at the resulting loaded table. Total_Curve always equals Sum(Sold), so it looks like my Peek('Total_Curve') is not returning anything? I also tried without the '' around Total_Curve and same result.
I think the problem is related to the fact that you are trying to accumulate values in the same load statement that's also aggregating using "Group BY".
The "Group By" syntax is quite demanding - you can only list key fields and aggregated functions there (hence the syntax error).
I think you need to aggregate the data first, and then reload it again using "Resident" load, to calculate your accumulated total.
A few things raise questions:
- what are you trying to do with sum(Previous(Sold)) ? I'm not sure what kind of results you might get from this function, in combination with the Group BY...
- What numbers are you trying to summarize under Total_Curve?
- Do you want your total to accumulate data across all Flights, or keep separate totals for different Flights?
Bottom line - separate accumulation and aggregation into 2 different statements...
