min (aggr(sum(revenue), customer)) isn’t working for the grand total

[herbert_beck]

Dear QlikView-Community,

I'm new to Qlikview and I'm trying to get the minimun revenue and the maximum revenue by customer dimension for each customer segment and the grand total.

a) Max (aggr(Sum(revenue), customer)) is working fine

b) Min (aggr(Sum(revenue), customer)) isn’t working in the grand total line of a table chart. It returns 0 although the sum(revenue) of the "smallest" customer is 232 EUR.

Kind regards,

Herbert

P.S.:

I'm currently working with the personal edition.

The data model is: customer segment > customer > order < product

A copy of the problem is:

@ min sum aggr

Nachricht geändert durch Herbert Beck

[herbert_beck]

Hi Michaël,

thank you very much for your reply. It's helpful and it works in the example which I attached to my question. Nvertheless in case the minimum customer's revenue of a segement is zero, I would like to see 0 and the grand total.

Kind regards,

Herbert

[agilos_mla]

I missed that cases...

THen use : Min (aggr(Sum({<customer_id=P(customer_id)>}revenue), customer_id))

The syntax P(Customer_id) will ensure to eliminate rows with missing values

Michael

[herbert_beck]

Hi Michaël,

what do you think of

Min (aggr(Sum({<revenue={"<>NULL()"}>}revenue), customer_id))

?

This works laso fine in the example that I attached to my question. The idea is that I would like to see a zero or a negative number in the total line, which should display the smallest revenue of a customer in all segments, depending on the data.

Kind regards,

Herbert

[herbert_beck]

Hi Michaël,

thanks! Good solution! works fine: )). How can i find this in help or a manual.

Kind regards,

Herbert

[agilos_mla]

Take a look at this nice doc.

[herbert_beck]

Hi Michaël,

I found the documentation of the P() function in set analysis decription of the QlikView-help. P() represents the set of possible values and E() the set of excluded values. This is convincing and working. Thanks,I will use this.

Kind regards,

Herbert

[herbert_beck]

Hi Michaël,

gorgoues manual, very helpful: )).

Kind regards,

Herbert