topic Re: Trying to understand Max date in dimension in QlikView

Trying to understand Max date in dimension

tinkerz1 — Fri, 27 Nov 2015 11:48:07 GMT

I dont understand why my max() on date dimension is producing an error in the date dimension

The reason I am avoiding set analysis is the get selected count expression that I want to swap between 2 list box entries.

Dimension:

max([Month-Year Item])

if(GetSelectedCount([Region])>0,[Region],[Location])

Expression:

if([Data group]='Item',count(distinct [ID]))

Re: Trying to understand Max date in dimension

Anonymous — Fri, 27 Nov 2015 12:01:48 GMT

try this:

Dimension:

max([Month]) ///what is year item??

if(GetSelectedCount([Region])>0,[Region],[Location])

Expression:

if([Data group]='Item',count(distinct [ID]))

Re: Trying to understand Max date in dimension

swuehl — Fri, 27 Nov 2015 12:04:04 GMT

You would need to use advanced aggregation (aggr() function ) in the dimension if you want to agreggate values.

Otherwise the scope for the aggregation is not clear.

I am not sure why you need to use the max() function to be able to use the second dimension. Could you upload a small sample QVW?

Re: Trying to understand Max date in dimension

nagarjuna_kotha — Fri, 27 Nov 2015 12:20:19 GMT

Hi Neli,

I didn't understand what exactly you are looking for?

Explain lttle bit more and expected outut.

Regards,

Nagarjuna

Re: Trying to understand Max date in dimension

tinkerz1 — Fri, 27 Nov 2015 12:35:43 GMT

I am uploading a file with a date for every record for that month, so I am trying to group on that date.

Month-Year Date Group Location Region ID

05-2015 Item Europe UK 1

05-2015 Item Europe UK 2

05-2015 Item USA NY 3

06-2015 Item Europe UK 4

06-2015 Item Europe UK 5

06-2015 Item USA NY 6

07-2015 Item Europe UK 7

07-2015 Item Europe UK 8

07-2015 Item USA NY 9

I am just after a count distinct ID for all Items in 07-2015 and Europe for the Location and getselectedcount(Region) is 0

Re: Trying to understand Max date in dimension

settu_periasamy — Fri, 27 Nov 2015 13:06:47 GMT

Hi,

Try the set analysis, Like

Dimension :

[Month-Year]

=if(GetSelectedCount([Region])>0,[Region],[Location])

Expression:

if([Data Group]='Item',count({<[Month-Year]={"$(=Max([Month-Year]))"}>}distinct [ID]))

May be try without set Analysis

Dimension :

=Aggr(Max([Month-Year]),[Data Group])

=if(GetSelectedCount([Region])>0,[Region],[Location])

Expression:

if([Data Group]='Item',count(distinct [ID]))