Evaluate all data in pivot table

[cpleon]

Hi!

I have a dataset with multiple numbers which identify an specific event during a period of time. This event might repeat over time, so, for example, I might get event 1 on January and February. I am using a Count Distinct function to identify the exact number of events per month,  using month on the columns of a table:

Event	Date
1	January
2	January
8	January
1	February
2	February
3	February
4	February
5	February
6	February
7	February

In this data set the result of my function would be:

January	February
3	7

What I would like to see is the distinct number of events evaluated during the beginning of the period and until the column date, in our example January would evaluate the distinct number of events during January only, but February would have to evaluate January and February with the following results:

January	February
3	8

Thanks

[rwunderlich]

Take a look at the Above() and Before() functions in the Help.

[cpleon]

Hi, I'm quite new on this. Could you elaborate a bit more on the subject?

[rwunderlich]

You posted this in New to QlikView. I just want to confirm you are using QlikView, not Qlik Sense, before I answer.

-Rob

[cpleon]

Yes, QlikView

[rwunderlich]

Here is the help for Above()

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

If you read that article, you'll see an example using RangeAvg() to calculate a running avg, which is close to what you are looking for.  You want to use RangeSum() to calc a running total

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

For the count parameter (how many rows above to accumulate) in RangeSum you can use RowNo() which represents the number of the current row.  Use can also use the Top() function rather than Above(). 

If you search "running total" on this forum you'll find some more examples.

-Rob