topic Re: Calculate average time for aggregated attributes in Pie Chart in QlikView

Calculate average time for aggregated attributes in Pie Chart

Wed, 07 Sep 2016 17:57:35 GMT

Hello all,

I need to build a pie chart that shows the average time (in days and months) between a Start and End Date for an aggregated set of status.

I’m trying to define the aggregation of Status values in the load script.

The attached file is a sample of what I’m trying to do and shows a pie chart that gives results, but without aggregation on Status attribute.

Could anyone help on this, please?

Many thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

sunny_talwar — Wed, 07 Sep 2016 18:10:51 GMT

What is the expected output? Can you provide some information on the expected output here?

Re: Calculate average time for aggregated attributes in Pie Chart

Thu, 08 Sep 2016 08:41:19 GMT

Hi Sunny,

Bases on the Qvw provided, my expected output is to have a pie chart with several attributes aggregated by status as follows:

Average time (in days and months) between a Start and End Date, If Status = '1', '2'

Average time (in days and months) between a Start and End Date, If Status = '25', '28'

Average time (in days and months) between a Start and End Date, If Status = '4'

Average time (in days and months) between a Start and End Date, If Status = '5'

Average time (in days and months) between a Start and End Date, If Status = '24'

Please let me know if you need further information

Thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

sunny_talwar — Thu, 08 Sep 2016 09:57:35 GMT

I am still not sure how you are getting these numbers? I am assuming that for Status =1, you would want the Avg(DayDiff) = 2 when we are on days? how are you getting 2 here? What is the logic? Also, you provided the output for Status = 1 and Status = 2 (I assume 25 was a typo and you meant 2 here), but what about the rest 4, 5, 24? and also what about others 3, 6?

Re: Calculate average time for aggregated attributes in Pie Chart

Anil_Babu_Samineni — Thu, 08 Sep 2016 10:07:59 GMT

Perhaps This

Avg ({<Days={'>=' & Weekday(Date(StartDate)) '<=' & Weekday(Date(EndDate))}, Months={'>=' & Weekday(Date(StartDate)) '<=' & Weekday(Date(EndDate))}, Status = {'1','2','25','28','4','5','24'>} Timestamp(time,'hh:mm:ss'))

Re: Calculate average time for aggregated attributes in Pie Chart

Thu, 08 Sep 2016 10:45:37 GMT

Ok. I will try to clarify.

Please look to the Qv application and sample excel file I send in attach to check the numbers and an idea of the expected result. To know if we are on Days or Months I’m thinking to consider ‘Days’ and ‘Months’ as a Cycle field that the user could choose to switch between Days and Months, then the graph should adjust the results according the selection.

Diff Days was an attribute created in a tentative to solve this. You could ignore if not required for your approach. 25 was a typo and the other status could be ignored. I only want to consider the values for Status as described inside the Qv application (Text Object)

I have a Start and End Date with a field Status related. Grouping the status according the logic described inside the Qv application (Text Object), I need to know the time spent when the status match the conditions.

Thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

Thu, 08 Sep 2016 10:54:52 GMT

Hi Anil,

Is not working. The expression is given error.

Thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

Thu, 08 Sep 2016 17:59:01 GMT

Hi Sunny,

Is there other input or information you need?

If so please let me know. I'm struggling to get this working but with no luck so far...

Thanks

Regards

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

Fri, 09 Sep 2016 16:23:36 GMT

Hi Sunny,

Any clue to help?

Thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

Anonymous — Fri, 09 Sep 2016 17:13:04 GMT

Paulo,

See if this is what you are looking for.

Fernando V.

Re: Calculate average time for aggregated attributes in Pie Chart

Mon, 12 Sep 2016 09:15:16 GMT

Hi Fernando,

Thanks. This is very close of what I'm looking for.

To avoid the inline table, imagine that I have a column with the Status Text in the data table. How can I aggregated them following the same logic, e.g. using a calculated dimension?

Thanks

Paulo

Re: Calculate average time for aggregated attributes in Pie Chart

Anonymous — Mon, 12 Sep 2016 13:14:57 GMT

Hi,

See the attached qvw.

Regards,

Fernando V.

Re: Calculate average time for aggregated attributes in Pie Chart

Mon, 12 Sep 2016 16:23:50 GMT

Hi Fernando,

Its working as expected. Thanks.