Summing count function in cyclic group

Tue, 23 Sep 2014 07:01:29 GMT

We have a calendar list box consisting of year, month, day and time of day. We have created a line chart where the dimensions is the cyclic group, Day_Week_Month (consisting of dimensions: day, week and month). To get the total number of reports successfully updated in a given time interval, we used the expression below:

=count({$< QVD_Id = {2, 9, 10, 11, 13, 15, 17, 18, 19, 20}>}DISTINCT QVD_Id)

This expression yields the correct results for day. When we select week or month from the cyclic group we would like to get results that shows the total sum of successfully updated reports for each week/month. As an example, on a daily basis there are 4 reports delivered successfully, therefore each week should yield 28, but with this expression (seen above) we only get about 10 reports per week.

In addition, in the dimension tab we have currently have the "Show All Values" box selected. If this box is not selected, the line chart doesn't show the points where no reports have been updated in the selected time interval. However, when we select week all it shows all the weeks in the year and we'd like to see the weeks in a month. As an example, if we have selected June in the calendar box and week from the cyclic group the chart displays all weeks of the year but only values that are different from zero for the weeks in June. We would like to show all points when day is selected from the cyclic group (this is achieved by selecting "show all values"), but when week or month is selected from the cyclic group, we only want to show the relevant weeks/months based on the selection in the calendar-list box (this is achieved by deselecting "show all values").

Does anybody know how to achieve this?

Appreciate the help!

Re: Summing count function in cyclic group

marcus_sommer — Tue, 23 Sep 2014 08:52:56 GMT

To sum daily counts over a month you could use aggr():

sum(aggr(count({$< QVD_Id = {2, 9, 10, 11, 13, 15, 17, 18, 19, 20}>}DISTINCT QVD_Id), Day))

By question two it could be that there are null-values within the calculation if no selection made and with selections no null-values or other errors occured. You will need a deeper view on your data, maybe on transactions level.

- Marcus

topic Re: Summing count function in cyclic group in Qlik Learning Discussions

Summing count function in cyclic group

Re: Summing count function in cyclic group