topic write max in dimension side in QlikView

write max in dimension side

Wed, 05 Mar 2014 06:06:38 GMT

Hi all,

I want to write max function in dimension side without using sum.Administrator

Re: write max in dimension side

tresB — Wed, 05 Mar 2014 06:10:00 GMT

If you mean calculated dimension, probably you have to use AGGR() function to use any aggregation function like your max().

Re: write max in dimension side

Wed, 05 Mar 2014 06:33:59 GMT

Hi,

I want difference between Posting date and max(calender date) ,

so how to write it in dimension side.

Re: write max in dimension side

jagan — Wed, 05 Mar 2014 06:41:03 GMT

Hi,

Try like this

=Rangemax([Posting date], [calender date]) - Will give you the max date in this two dates

= Date ([Posting date] - [calender date]) - Will give you the difference between the two dates

Can you attach some sample data.

Regards,

Jagan.

Re: write max in dimension side

MK_QSL — Wed, 05 Mar 2014 06:54:06 GMT

If you give us some more information, will be useful to give you right direction..

Re: write max in dimension side

Wed, 05 Mar 2014 06:56:58 GMT

I need the no of days between the posting date and max CalenderDate.

I have a calender in my report where I will select for eg. 7/31/2013
and my database has the field Posting Date with different dates.
So for each posting date I need the no of days with Calender Date.
eg.
1. Posting date is 6/25/2013.
So no of days between 7/31/2013 and 6/25/2013 is 35 days.

2. Posting date is 7/20/2013.
So no of days between 7/31/2013 and 7/20/2013 is 10 days.

So my calender will always be same until user changes the date selection in the calender.

Please see the sample data.
CalenderDate: 07/31/2013

Posting Date	No of Days
07/31/2013	1
07/30/2013	0
07/29/2013	-1
07/28/2013	-2
07/27/2013	-3
07/26/2013	-4
07/25/2013	-5
07/24/2013	-6
07/23/2013	-7
07/22/2013	-8
07/21/2013	-9
07/20/2013	-10
07/19/2013	-11
07/18/2013	-12
07/17/2013	-13
07/16/2013	-14
07/15/2013	-15
07/14/2013	-16
07/13/2013	-17
07/12/2013	-18
07/11/2013	-19
07/10/2013	-20
07/09/2013	-21
07/08/2013	-22
07/07/2013	-23
07/06/2013	-24
07/05/2013	-25
07/04/2013	-26
07/03/2013	-27
07/02/2013	-28
07/01/2013	-29
06/30/2013	-30
06/29/2013	-31
06/28/2013	-32
06/27/2013	-33
06/26/2013	-34
06/25/2013	-35

So I need to create this No of days column on dimensions side in Pivot table.

Re: write max in dimension side

MK_QSL — Wed, 05 Mar 2014 07:03:50 GMT

Is number of days is field or it is a result from some of your calculation?

Re: write max in dimension side

Wed, 05 Mar 2014 07:06:47 GMT

it should be a calulated dimension

Re: write max in dimension side

jagan — Wed, 05 Mar 2014 07:09:01 GMT

Hi,

Try Interval() like this

=Interval[Posting date] - [calender date], 'D')

Regards,

jagan.

Re: write max in dimension side

MK_QSL — Wed, 05 Mar 2014 07:11:58 GMT

No my question is NO OF DAYS need to be calculated based on POSTING DATE?

if yes, you can use like below..

Dimension = [Posting Date]

Expression

Max(CalendarDate)-[Posting Date]+1

Max(CalendarDate)-[Posting Date]

Re: write max in dimension side

Wed, 05 Mar 2014 07:15:38 GMT

Hi jagan,

when I will try this expression Interval[Posting date] - [calender date], 'D') I will get difference between every calander date. I want Max(calenderDate).

When I will try this : Interval[Posting date] - max[calender date], 'D') then I will get error in formula.

Re: write max in dimension side

MK_QSL — Wed, 05 Mar 2014 07:19:30 GMT

Interval(Max([Calendar Date])-[Posting Date],'D')

Re: write max in dimension side

Wed, 05 Mar 2014 07:26:11 GMT

NO still it is giving errror in formula.

Re: write max in dimension side

jagan — Wed, 05 Mar 2014 07:27:15 GMT

Hi,

Can you attach sample file, so that it is easier to give solution.

Try this

Aggr(Interval([Posting date] - [calender date], 'D'), [Posting date], [calender date])

Regards,

Jagan.