topic Re: Maximum Value for Certain Time Duration in QlikView

Maximum Value for Certain Time Duration

MK_QSL — Mon, 02 Mar 2015 17:50:14 GMT

DON'T WANT SOLUTION BY SCRIPTING !!

Front End Only !

We have Daily Sales Value with below dimensions.

SalesTime, SalesAmount

02/03/2015 08:10:00, 120

02/03/2015 08:20:00, 130

02/03/2015 08:30:00, 140

02/03/2015 08:40:00, 100

02/03/2015 08:50:00, 10

02/03/2015 09:00:00, 110

02/03/2015 09:10:00, 75

02/03/2015 09:18:58, 80

02/03/2015 09:20:00, 85

02/03/2015 09:22:17, 40

02/03/2015 09:22:34, 30

02/03/2015 09:23:12, 120

02/03/2015 10:36:49, 100

02/03/2015 10:39:49, 80

02/03/2015 10:40:00, 85

02/03/2015 10:50:00, 120

02/03/2015 13:12:59, 140

02/03/2015 13:13:21, 200

02/03/2015 13:15:18, 220

02/03/2015 13:18:13, 110

02/03/2015 13:18:38, 100

02/03/2015 13:18:46, 140

What I want is to find the Maximum SalesAmount for User Selected Duration..

i.e.

Sales

02/03/2015 08:00 to 02/03/2015 08:30 SalesAmount

02/03/2015 08:30 to 02/03/2015 09:00 SalesAmount

02/03/2015 09:00 to 02/03/2015 09:30 SalesAmount

02/03/2015 09:30 to 02/03/2015 10:00 SalesAmount

........

Now want to find out which duration has got the maximum SalesAmount and What is that SalesAmount..

The duration is also user selected so can't use the fixed duration version..

Duration could be 15 Mins, 30 Mins, 45 Mins, 60 Mins etc

Thanks and looking forward for someone to reply on this...

Re: Maximum Value for Certain Time Duration

Gysbert_Wassenaar — Mon, 02 Mar 2015 18:17:51 GMT

See attached qvw.

Re: Maximum Value for Certain Time Duration

MK_QSL — Mon, 02 Mar 2015 20:32:57 GMT

Hi Gysbert,

Thanks for your reply.

But this is actually not what I am looking for.

Let me explain in little depth.

Consider that User has selected 30 mins.

So the time slot would be something like

08:00 - 08:30

08:30 - 09:00

09:00 - 09:30 etc.

Now for each slot there would be Amount Total.

I need in Text box the Max Sales Amount from above slot.

The slot is Dynamic of course !

If we create below chart...

Calculated Dimension

=timestamp(Class(SalesTime, $(vDuration)/1440),'DD/MM/YYYY hh:mm:ss') & ' to ' & timestamp(Class(SalesTime, $(vDuration)/1440) + $(vDuration)/1440 ,'DD/MM/YYYY hh:mm:ss')

Expression

SUM(SalesAmount)

We will get 910 for 03/02/2015 13:00:00 to 03/02/2015 13:30:00

I need 910 in Text Box..

Hope this is enough to give answer.

Thanks in advance.

Re: Maximum Value for Certain Time Duration

Gysbert_Wassenaar — Tue, 03 Mar 2015 09:07:58 GMT

I don't think a text box is possible. If you had a field with the intervals you could use that field in an aggr function, but you can't use a calculated dimension in the aggr function. But you can use a straight table and use dimension limits to show only the top 1 largest value. You can format the straight table to hide the caption and header row, etc. The result can look just like a text box.

Re: Maximum Value for Certain Time Duration

MK_QSL — Tue, 03 Mar 2015 10:49:32 GMT

I also tried a lot and only after that I raised a question in Community. Anyway, just want to confirm that is that possible or not...

Thanks for your reply and time.

Re: Maximum Value for Certain Time Duration

Gysbert_Wassenaar — Tue, 03 Mar 2015 11:05:18 GMT

As I said, I don't think a text box is possible, but a straight table using dimension limits is.

Re: Maximum Value for Certain Time Duration

MK_QSL — Tue, 03 Mar 2015 11:11:20 GMT

Actually I was using a Trellis Bar Chart where TimeStamp is used with Class Function.

To give the static max and min I need this...

But never mind, right now I will use the Static Value based on the last three years available data.

Re: Maximum Value for Certain Time Duration

anbu1984 — Tue, 03 Mar 2015 14:05:28 GMT

I removed date from your input and tried the below expr. This is not the solution for your query.

vStrtTime=480

vDuration=30

=Rangemax(Sum(If(SalesTime >= vStrtTime/1440 And SalesTime <= ((vStrtTime+vDuration)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration)/1440) And SalesTime <= ((vStrtTime+vDuration*2)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*2)/1440) And SalesTime <= ((vStrtTime+vDuration*3)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*3)/1440) And SalesTime <= ((vStrtTime+vDuration*4)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*4)/1440) And SalesTime <= ((vStrtTime+vDuration*5)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*5)/1440) And SalesTime <= ((vStrtTime+vDuration*6)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*6)/1440) And SalesTime <= ((vStrtTime+vDuration*7)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*7)/1440) And SalesTime <= ((vStrtTime+vDuration*8)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*8)/1440) And SalesTime <= ((vStrtTime+vDuration*9)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*9)/1440) And SalesTime <= ((vStrtTime+vDuration*10)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*10)/1440) And SalesTime <= ((vStrtTime+vDuration*11)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*11)/1440) And SalesTime <= ((vStrtTime+vDuration*12)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*12)/1440) And SalesTime <= ((vStrtTime+vDuration*13)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*13)/1440) And SalesTime <= ((vStrtTime+vDuration*14)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*14)/1440) And SalesTime <= ((vStrtTime+vDuration*15)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*15)/1440) And SalesTime <= ((vStrtTime+vDuration*16)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*16)/1440) And SalesTime <= ((vStrtTime+vDuration*17)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*17)/1440) And SalesTime <= ((vStrtTime+vDuration*18)/1440),SalesAmount)),

Sum(If(SalesTime > ((vStrtTime+vDuration*18)/1440) And SalesTime <= ((vStrtTime+vDuration*19)/1440),SalesAmount))

)