Filter the first value in a data set.

Mon, 02 Jun 2014 15:10:22 GMT

Hi,

I hope someone can help me with this issue I'm having trying to filter the first value in a dataset. I haved a sample set of data as per the table below. Im using some standard functions with set values but I'm trying to filter one and it's not working.

My first expression is sum(BudgetValue) with Month as the dimension. When a user selects a Cycle Month they get the Month Values for that cycle and this works correctly.

My second expression gets the month values for the previous cycle with the following expression

sum({<CycleID={$(=CycleID-1)} BudgetValue) and this also works.

The expression thats not working is when I try to combine the 2 expressions above but only add the first value from the previous cycle. For example, if a user selects Cycle Month Mar (Cycle ID 3), I want my table to display

Feb Mar Apr May Jun

240 230 270 240 220

Using the following expression sum(BudgetValue) + sum({<CycleID={$(=CycleID-1)} I am getting the following

Feb Mar Apr May Jun

240 450 540 490 440

i.e. it is adding all of the budget values for CycleID 3 and 2. I only want to add the first value for CycleID 2 to all of the values for CycleID 3. I have trying to use the First function and FirstSortedValue function but this hasn't worked.

Can anyone advise on what function / set function I could use to get my desired results.

Thanks.

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7
Cycle ID	1	2	3	4	5	6
Cycle Month	Jan	Feb	Mar	Apr	May	Jun
Jan	200	250	240	280	230	210
Feb		240	220	270	250	220
Mar			230	270	240	220
Apr				280	250	240
May					240	230
Jun						220

topic Filter the first value in a data set. in QlikView

Filter the first value in a data set.