topic Re: MAX (Total) not working in QlikView

MAX (Total) not working

Wed, 13 Mar 2013 13:09:01 GMT

Hi,

I have a table of inventory items transactions, I'm trying to have a pivot table per item and other dimensions to get the unit cost per item for the last transaction done per date, but its working only for one item selected, but for all items it result with zero, following expression had been used

sum(if(trasaction_date=MAX(TOTAL transaction_date),trasaction_cost,0))

if someone can help on this issue, thank you

Firas Ahmed

Re: MAX (Total) not working

CELAMBARASAN — Wed, 13 Mar 2013 13:14:03 GMT

Can you upload sample file? It helps to understand your problem easier.

You may need to use Aggr function.

Re: MAX (Total) not working

VishalWaghole — Wed, 13 Mar 2013 13:16:49 GMT

Hi,

Please try this one.

sum(if(trasaction_date=aggr(MAX(TOTAL transaction_date),item)trasaction_cost,0))

Regards,

VIshal

Re: MAX (Total) not working

narender123 — Wed, 13 Mar 2013 13:17:48 GMT

You can also try this one as:

if(trasaction_date=MAX(TOTAL transaction_date),sum(trasaction_cost),0)

Regards:

Narender

Re: MAX (Total) not working

Wed, 13 Mar 2013 13:18:36 GMT

HI Ahmed,

First you store the MAX transaction_date in to a variable.

You can use this variable in the expression, it will be eaiser way.

Regards,

Yaseen

Re: MAX (Total) not working

Anonymous — Wed, 13 Mar 2013 13:18:44 GMT

use this

=sum(aggr(max(date),Transation_id)transaction_cost)

Best Regards,

Anant

Re: MAX (Total) not working

jonathandienst — Wed, 13 Mar 2013 13:45:23 GMT

Something like this:

Sum({<trasaction_date = {"$(=Date(Max(transaction_date)))"} trasaction_cost)

Hope that helps

Jonathan

Re: MAX (Total) not working

Sat, 16 Mar 2013 04:58:23 GMT

thank you Vishal, statement did not solve the issue, it returns zero always (one or all items), it may missing one more parameter

Re: MAX (Total) not working

Sat, 16 Mar 2013 05:03:17 GMT

thank you all for reply, but actually it did not work, all of them still return zero, I would appreciate your help I will brief issue again just in case:

table structure (item, quantity, cost, transaction date)

dashboard target : inventory aging

current issue: to get the cost of the last transaction per item as per date dimension selected

regards

Firas

Re: MAX (Total) not working

Sat, 16 Mar 2013 05:39:26 GMT

Can you upload your Sample Application

Re: MAX (Total) not working

Sat, 16 Mar 2013 07:27:59 GMT

thank you nirmal, can you pls. advice how to upload?

Re: MAX (Total) not working

nirav_bhimani — Sat, 16 Mar 2013 10:20:45 GMT

Hi,

You can use the advanced editor to attach files to your posts.

You will find this on the top right of your the text area (see the image for reference)

And Browse for your files using the Browse button.

Regards,

Nirav Bhimani

Re: MAX (Total) not working

Sat, 16 Mar 2013 10:29:30 GMT

pls. find attached a sample data

Re: MAX (Total) not working

kaushiknsolanki — Sat, 16 Mar 2013 10:40:32 GMT

Hi,

This has a section access, kindly give us user name and password of user and admin

Regards,

Kaushik Solanki

Re: MAX (Total) not working

Sat, 16 Mar 2013 11:16:43 GMT

admin/noaccess