topic Re: Date as a Percentage in QlikView

Date as a Percentage

evansabres — Tue, 15 Nov 2016 21:47:54 GMT

I am attempting to create a chart to visualize sales trend for different events that have different lengths of time that they were on-sale for. However, one event could possibly have an on-sale length of 40 days whereas another could have an on-sale length of 200 days. So, in an attempt to normalize these periods of time, I am wondering if it would be possible to create a time dimension that is a percentage of time elapsed.

My fields are eventDTE (actual date of the event date) transDTE (transaction date) onsaleDTE (date tickets went on sale) and netSoldQTY (number of tickets sold)

My expression is straightforward, =NUM(SUM(netSoldQTY),'#,###.')

Any thoughts on how I would create the time dimension?

An example of a possible scenario:

eventDTE = 10/13/2016

transDTE = 10/01/2016

onsaleDTE = 9/17/2016 (this is constant)

Re: Date as a Percentage

MarcoWedel — Tue, 15 Nov 2016 22:58:29 GMT

can you describe your calculation and expected result given some more sample data?

Re: Date as a Percentage

johnw — Tue, 15 Nov 2016 23:59:17 GMT

So 0% would be transDTE = onsaleDTE, and 100% would be transDTE = eventDTE?

(transDTE - onsaleDTE)/(eventDTE - onsaleDTE) as PercentElapsed

But it seems like to be useful as a dimension for a chart, you'd need to group these, like 0%-10%, 10%-20%, and so on? Whatever interval makes sense. Here's 10%.

class((transDTE - onsaleDTE)/(eventDTE - onsaleDTE),.1) as PercentElapsedRange

Re: Date as a Percentage

evansabres — Wed, 16 Nov 2016 01:54:40 GMT

Your assumption (So 0% would be transDTE = onsaleDTE, and 100% would be transDTE = eventDTE?) is correct.

Will try out your suggestion and reply to thread.

Thanks as always for assistance.