topic Max function in QlikView

Max function

Thu, 19 Sep 2013 16:17:32 GMT

If I have 12 months in separate columns, and I want to find the highest sales amount by month? how would I do this?

Re: Max function

Thu, 19 Sep 2013 16:29:47 GMT

Hi Daniel

If you are using a straight table and these the months are split by dimension (i.e. you only have one expression) then under expressions the Total Mode can be switched to Max which will give you what you need.

Otherwise there are plenty of threads on this subject, I think the one below would be useful;

Another max row question

I hope this helps

Steve

Re: Max function

Fri, 20 Sep 2013 08:34:39 GMT

Hi Thanks for your help, My problem is slightly different I think as my table looks like the below

Entity	JAN	FEB	MARCH	JUNE	JULY	AUG
A	0	0	0	10	0	0
B	5	0	0	2	10	0
C	15	0	5	0	0	0

I want to be able just have a text box probably that dsiplays 15 as the max, is this possible??

Re: Max function

sujeetsingh — Fri, 20 Sep 2013 08:41:54 GMT

Wale ,

i have some queries

--You want it to be displayed in UI level .

--or you want it to done in scripting.

Re: Max function

Fri, 20 Sep 2013 08:43:46 GMT

I want it to be at UI level

but if its possible in scripting as well that is fine as long as I get to the answer in the end.

Re: Max function

Fri, 20 Sep 2013 09:11:19 GMT

You can use max function with Sum(Total (Field)).

Total() is the function which avoid the effect of dimesions as you want that 15 is not affected by the dimensions in the straight table.

Re: Max function

Fri, 20 Sep 2013 09:23:59 GMT

Thanks, I am still a bit confused of what the expression would look like, could you give me an example using the headers from the above table please?

Re: Max function

tresB — Fri, 20 Sep 2013 09:24:55 GMT

Hello Daniel,

Use crosstable() to upload the data like: Crosstable (Month, Amount,1) Load * from <Your Data source>;

Then use expression: =Max(Amount) in a text object.

Sneha Topre, I am sorry to poke again, but, total is not a function, it's a qualifier/keyword. So you can't write like:

Sum(Total(Field))), but, = Sum(Total field)

Update: PFA

Re: Max function

Fri, 20 Sep 2013 09:27:28 GMT

Thanks tresesco to correct me,Yo are correct in saying its a Keyword, i used it forgot exact syntax..

Re: Max function

sujeetsingh — Fri, 20 Sep 2013 09:31:13 GMT

See this sample for reference,

Re: Max function

Fri, 20 Sep 2013 09:32:59 GMT

You can use like =Sum(Total Amount).

Re: Max function

Fri, 20 Sep 2013 09:34:17 GMT

Thanks, The actual data set is very large 53 weeks across the top around 120 entities, took a look at your script, would I have to use the inline statement in the same way? CrossTable (Months, Sales,1) Load * Inline [ Entity, Jan, feb, Mar, Apr A, 1, 2, 3, 4 b, 10, 2, 30, 45 c, 5, 6, 7, 8 d, 6, 7, 8, 1 ]

Re: Max function

tresB — Fri, 20 Sep 2013 09:39:53 GMT

No. Inline is used to take sample data. You can replace the inline part like:

Crosstable(Months, Values, 1)

Load * From <YourDatasource>;

Re: Max function

Fri, 20 Sep 2013 09:46:14 GMT

what this Crosstable() do..?

Re: Max function

tresB — Fri, 20 Sep 2013 10:32:28 GMT

Hi Sneha,

Crosstable is used to transform table structure during load. For more information, please refer QlikView Reference Manual or help available with qv software.