topic Re: max does not work inside set analysis in QlikView

max does not work inside set analysis

Wed, 07 Sep 2016 11:39:21 GMT

Hi,

I am making a pivot table based on the following data.

Customer Transaction Date Amount

A Sales Order 08/11/2016 123

B Sales Order 08/12/2016 234

B Sales Order 08/16/2016 546

C Sales Order 07/01/2016 100

C Sales Order 07/02/2016 200

A Invoice 08/15/2016 300

B Invoice 08/15/2016 300

dimension:

Customer

expression:

1. sum({<Transaction={'Sales Order'}, Date={'$(=max({<Transaction={'Sales Order'}>}Date))'}>}Amount)

the expected output is

A 123

B 546

C 200

the actual output is

B 546

the "max({<Transaction={'Sales Order'}>}Date)" in the above expression is not considering the customer dimension and returns the max date for all Sales Orders and so B shows up because it has max date for the sales order. To my surprise this expression is controlling the dimension.

but when i just make a column on max({<Transaction={'Sales Order'}>}Date) then i get the correct value

A 08/11/2016

B 08/16/2016

C 07/02/2016

I am sure i am missing something here.

Advise please.

Regards

sramya

Re: max does not work inside set analysis

Anonymous — Wed, 07 Sep 2016 11:45:02 GMT

try like this?

=aggr(max({<Transaction={'Sales Order'}>}Amount) , Customer)

Re: max does not work inside set analysis

Wed, 07 Sep 2016 12:05:35 GMT

But it does not consider the max sales order date?

Re: max does not work inside set analysis

maxgro — Wed, 07 Sep 2016 12:08:15 GMT

FirstSortedValue({$ <Transaction={'Sales Order'}>} Amount, -Date)

Re: max does not work inside set analysis

tresB — Wed, 07 Sep 2016 12:08:25 GMT

Try this:

FirstSortedValue({<Transaction={'Sales Order'}>} Amount, -Date)

Re: max does not work inside set analysis

Chanty4u — Wed, 07 Sep 2016 12:08:35 GMT

try like

=FirstSortedValue(OrderDate,-Customer)

=FirstSortedValue(Customer,-OrderDate)

Re: max does not work inside set analysis

marcus_sommer — Wed, 07 Sep 2016 12:13:21 GMT

Your max-expression within the $-sign expansion creates an adhoc-variable which could have only one value (which will be calculated before the chart is calculated) and applied for each row. This meant you need a different approach, maybe something like this:

firstsortedvalue({<Transaction={'Sales Order'}>} Amount, -Date)

- Marcus

Re: max does not work inside set analysis

Wed, 07 Sep 2016 12:17:34 GMT

it does not work and returns null value

Re: max does not work inside set analysis

tresB — Wed, 07 Sep 2016 12:22:08 GMT

Possibly because you have multiple values against max date in real data. Try:

FirstSortedValue( Aggr(Sum({<Transaction={'Sales Order'}>} Amount), Date), -Date)

Re: max does not work inside set analysis

effinty2112 — Wed, 07 Sep 2016 12:28:10 GMT

Hi Saurabh,

As Marcus explains the set returned by your set analysis expression is evaluated outside the chart, so to speak and then it is applied to the chart as a whole. So your expression becomes:

sum({<Transaction={'Sales Order'}, Date={'08/16/2016'}>}Amount)

and this is applied to all rows and so only this line is considered:

Customer Transaction Date Amount

B Sales Order 08/16/2016 546

Cheers

Andrew

Re: max does not work inside set analysis

Wed, 07 Sep 2016 12:55:15 GMT

in all these expressions, we are not considering one fact that i need to consider date of only sales orders and not of the invoices.

above expressions perhaps will give amount only if there is a sales order on the first sorted value of date which may not be always true.

i may have last sales order on 08/11 but the first sorted value of date may 09/11.

Does it make sense to you ?

Re: max does not work inside set analysis

jonathandienst — Wed, 07 Sep 2016 13:00:26 GMT

That's why the last parameter of the FirstSortedValue is negative ( -Date), so you are evaluating "LastSortedValue"

And Marcus' expression filters for seales orders:

=firstsortedvalue({<Transaction={'Sales Order'}>} Amount, -Date)

Re: max does not work inside set analysis

tresB — Wed, 07 Sep 2016 13:02:12 GMT

This?

FirstSortedValue({<Transaction={'Sales Order'}>} Aggr(Sum({<Transaction={'Sales Order'}>} Amount), Date), -Date)

Re: max does not work inside set analysis

sunny_talwar — Wed, 07 Sep 2016 13:41:01 GMT

Here is a sample that might help you out here.

Expression:

=FirstSortedValue({<Transaction = {'Sales Order'}>}Amount, -Date)

Re: max does not work inside set analysis

Thu, 08 Sep 2016 13:23:49 GMT

so the set analysis applies to the sorted weight that Date here and not to the expression which is amount? Can you please why does it work?

Re: max does not work inside set analysis

sunny_talwar — Thu, 08 Sep 2016 15:00:25 GMT

sramya1234‌ I am not sure I understand your question, would you be able to clarify what exactly do you not understand about this expression

Re: max does not work inside set analysis

Thu, 08 Sep 2016 17:55:24 GMT

Do the if you just write firstSortedValue(Amount, -Date) then it will sort all the dates and get the amount of the greatest date.

but when you do the expression firstSortedValue({<Transaction={''Sales Order'}>}Amount, -Date) then it takes amount of the greatest date of all sales orders…right?

So my question is...

once you have instead set analysis if the firstSortedValue then does it...

1. Sort only SalesOrder's date

2. Sorts all dates but take the amount from the sale order..

How does it work?