topic select the max value by date in QlikView

select the max value by date

Wed, 14 Sep 2011 07:29:32 GMT

Hi,

Need some helps here.

I'm trying to show the max "Rows" value by date.

For example:

The max row for abc.qvw is on 19-09, it should show 23.

then for xyz.qvw, the max row is on 18-09, so it should show 55.

select the max value by date

Wed, 14 Sep 2011 07:33:45 GMT

Hi,

Use Maxstring function in place of sum

HTH

Reg,

Shubhu

select the max value by date

Wed, 14 Sep 2011 07:36:26 GMT

In fact, i have tried so many possible ways.

None works....

so i suspect, it's either not possible to do it with that structure, or we need to manipulate the data further.

this expression works if only there's only one date row. when u have multiples, it wont work.

MaxString(if(DATE=aggr(maxstring(DATE),QVW),ROWS))

Re: select the max value by date

kaushiknsolanki — Wed, 14 Sep 2011 07:48:21 GMT

Hi,

Have a look at the attachment.

Regards,

Kaushik Solanki

Re: select the max value by date

Sokkorn — Wed, 14 Sep 2011 07:52:52 GMT

Hi,

Try to change from sum(Rows) to max(Rows).

Regards,

Sokkorn

Re: select the max value by date

Wed, 14 Sep 2011 07:55:41 GMT

Hi,

Check the attached doc.

Reg,

Shubhu

select the max value by date

Wed, 14 Sep 2011 09:02:34 GMT

Hi Kaushik,

Thanks, but i need it in the table.

select the max value by date

Wed, 14 Sep 2011 09:03:22 GMT

Hi Shubhu,

Neat, but the data is wrong. the 55 is on 18-09. not 19-09.

Thanks.

Re: select the max value by date

kaushiknsolanki — Wed, 14 Sep 2011 09:12:05 GMT

Hi,

OK have a look at the file.

Regards,

Kaushik Solanki

select the max value by date

Wed, 14 Sep 2011 09:26:19 GMT

Hi Kaushik,

Thanks agian, but the max() wont work if we want QVD | DATE | ROWS in the table.

Any other tips?

select the max value by date

kaushiknsolanki — Wed, 14 Sep 2011 09:48:51 GMT

Hi,

Ok you tell us what output you want, with example.

Regards,

Kaushik Solanki

select the max value by date

Wed, 14 Sep 2011 10:05:34 GMT

ok, In the final result, we should have the following...

Re: select the max value by date

kaushiknsolanki — Wed, 14 Sep 2011 10:18:58 GMT

Hi,

Have a look at this example.

Here if you see the data, the QVW has 19 as the max date.

Regards,

Kaushik Solanki

select the max value by date

Wed, 14 Sep 2011 11:52:41 GMT

yes, but the data is wrong. The 55 should be on 18-09. not 19-09.

select the max value by date

kaushiknsolanki — Wed, 14 Sep 2011 12:10:51 GMT

Hi,

You are right,

But for xyz.qvw your max date is 19th and the max Rows is 55. (This max rows are irrespective of date)

If you want Max Rows for only Max Day.. Meaning For example consider you have data like this.

QVW Date Rows

ABC 19 25

ABC 20 20

XYZ 19 35

XYZ 20 40

SO when you say you want max date and max rows. the output will be

ABC 20 25

XYZ 20 40

But when you say you want max rows for max day. then output will be

ABC 20 20

XYZ 20 40

So now tell me what scenario from this you are looking for.

Regards,

Kaushik Solanki

Re: select the max value by date

SunilChauhan — Wed, 14 Sep 2011 12:21:48 GMT

see the attched file

chart

Max by date shows you max by date

hope this help you

Re: select the max value by date

Thu, 15 Sep 2011 08:44:02 GMT

Please check my attached file.

do let me know if this one help you.

select the max value by date

Thu, 15 Sep 2011 12:16:53 GMT

Hi there,

Thanks for the correct answer.

What does the negative sign means infront of column name Rows?

select the max value by date

Thu, 15 Sep 2011 16:01:28 GMT

Nicksatch,

The Help for FirstSortedValue says:

"...By preceding the sort-value expression with a minus sign, the function will return the last value instead."