topic Re: Evaluate all data in pivot table in QlikView

Evaluate all data in pivot table

cpleon — Wed, 31 Mar 2021 15:26:36 GMT

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

Re: Evaluate all data in pivot table

rwunderlich — Wed, 31 Mar 2021 17:13:03 GMT

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

Re: Evaluate all data in pivot table

cpleon — Wed, 31 Mar 2021 19:14:15 GMT

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

Re: Evaluate all data in pivot table

rwunderlich — Wed, 31 Mar 2021 19:57:38 GMT

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

-Rob

Re: Evaluate all data in pivot table

cpleon — Wed, 31 Mar 2021 20:36:49 GMT

Yes, QlikView

Re: Evaluate all data in pivot table

rwunderlich — Wed, 31 Mar 2021 22:29:22 GMT

Here is the help for Above()

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

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/RangeFunctions/rangesum.htm

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