topic Re: Max month data in QlikView

Max month data

Fri, 18 Sep 2015 07:27:18 GMT

Hi guys,

in my excel I want to display sales of A or B by max month

here for A sales by maxmnth is 10 , but for B sales by max month is 70.

I am using sum({<Month={'$(vMaxmnth)'>}Sales) in exp and in dimension it is Customer.

But for B it is working but for A it is not working.

Any suggestion on this? If multiple mnth entetred for customer it should pick max mnth sales but if only one mnth entered it should take that particular row.

Can you help me on this?

Re: Max month data

Fri, 18 Sep 2015 07:29:14 GMT

Hi,

Can you tell still more clearly.

Thanks

Harshitha

Re: Max month data

Fri, 18 Sep 2015 07:47:21 GMT

i want to show sum(sales) by max mnth in exp and in dimension Customer.

Re: Max month data

Fri, 18 Sep 2015 07:50:57 GMT

Hi,

=sum({<Year={$(=max(Year))},Month={$(=max({<Year={$(=max(Year))}>}Month))}>}sales)

Try out with this expression.

Re: Max month data

Fri, 18 Sep 2015 08:00:04 GMT

Hi,

Tried with the above expression is it working fine or not.

Thanks

Harshitha

Re: Max month data

Fri, 18 Sep 2015 08:01:51 GMT

i already hav year month calculated, so i am using that in my expression i am trying sum({<Month={'$(vMaxmnth)'>}Sales) and in dimention customer.

But it does not work for all the cases

Re: Max month data

Anonymous — Fri, 18 Sep 2015 08:04:41 GMT

do you want his:

then use the follwoing Expression

=aggr(max(Sales),Customer)

while Customer and Month are Dimensions

Re: Max month data

jonathandienst — Fri, 18 Sep 2015 08:07:50 GMT

The set expression is evaluated once for the table, not once per row, so for A, the expression reports A in August, which is zero as there is no data for A in August.

The best solution is to add a last sales flag during reload. Something like

Data:

LOAD Month,

Customer,

Sales,

...

FROM ...

Join(Data)

LOAD Customer,

Max(Month) As Month,

1 As LastSale

Resident Data

Group By Customer;

Now your expression becomes:

=Sum({<LastSale={1}>} Sales)