topic Re: Top(Max value) discarding others in QlikView

Top(Max value) discarding others

Anonymous — Wed, 12 Nov 2014 03:37:20 GMT

Hi,

I am attaching the sample app and go through it to find top(Max) values and discarding others in a pivot table

thanks in advance

Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 03:51:30 GMT

QlikView does not identify your field as timestamp.

Try to edit your timestamp format like that - then you can use max() functions in your chart table

Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 03:55:50 GMT

please find attached

Re: Top(Max value) discarding others

Anonymous — Wed, 12 Nov 2014 04:03:24 GMT

Daniel,

Thanks for your time

but I need to show only max(date) values. I don't want all the dates

Re: Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 04:11:21 GMT

Try to remove the Start field from the Dimensions...please find attached

Re: Re: Re: Top(Max value) discarding others

Anonymous — Wed, 12 Nov 2014 04:19:45 GMT

Daniel,

Kindly find the attachment

I need the values for that date

Re: Re: Re: Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 04:40:22 GMT

Please find attached

Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 04:43:02 GMT

I used the Date again as dimension but with the following expression:

if(Start=max(total Start),Start)

Re: Re: Re: Top(Max value) discarding others

Anonymous — Wed, 12 Nov 2014 04:45:44 GMT

Daniel,

Could you provide the sample app

I could not able to fix it from my end

Re: Top(Max value) discarding others

danieloberbilli — Wed, 12 Nov 2014 04:47:14 GMT

its attached in the above posting

Re: Top(Max value) discarding others

ashishkalia — Wed, 12 Nov 2014 05:28:31 GMT

HI Jonh
Jst do one thing

TAB1:

LOAD GRADE&'-'StartDate AS Key,

max(Num(EndDate)) AS TopDate

RESIDENT TABLE_X

GROUP BY GRADE, StartDate;

noconcate

LOAD Num#(SubField(Key,'-',0) ) AS GRADE,

TIMESTAMP(NUM#(SubField(Key,'-',-1))) as StartDate,

TIMESTAMP(TopDate) as TopDate

RESIDENT TAB1;

u can DROP tab1 if u want or aliase so that doesnt make synthetic or else

ND NOW U CAN DIRECTLY TAKE THESE FIELDS INTO UR CHARTS.........

CHEERS.

Re: Re: Re: Re: Top(Max value) discarding others

jagan — Wed, 12 Nov 2014 06:02:01 GMT

Hi,

Please find attached file for solution.

Regards,

jagan.

Re: Top(Max value) discarding others

daveamz — Wed, 12 Nov 2014 18:04:14 GMT

Hi, John!

See the example attached. I used another table based on the resident one to mark the max values and then, in the pivot table, you can simply filter out unwanted values with set analysis. If you don't need the other values at all, you can INNER JOIN instead of LEFT JOIN.

Best regards,

David