topic Re: Set analysis question about max function in QlikView

Set analysis question about max function

Thu, 12 Jun 2014 23:52:15 GMT

HI,

I got one pivot table which looks like this

The last expression is defined as sum({<Transactions.TxnDate={$(=Max(Transactions.TxnDate))}>}Amount)

Here is the question...

When i use max(Transactions.TxnDate) alone as a expression then it shows 1/1/2014 an fit is correct because i have one transaction on that day.

But when it is used in a set analysis then it gives me the last day of 1/31/2014....I expect it to return the same result whether it is used in set analysis or alone.

I also have a master calendar in this document so 1/31/2014 does exist in data model but there is no transaction on that day.

Can you please help understand this behavior?

Regards,

Saurabh

Re: Set analysis question about max function

SergeyMak — Fri, 13 Jun 2014 00:54:52 GMT

Hi,

you can see in your table the second line with Max(Transactions.TxnDate)="1/31/2014"

When you use $-sign expansion you get the maximum for whole data set, which is in your app "1/31/2014"

Re: Set analysis question about max function

Anonymous — Fri, 13 Jun 2014 00:57:53 GMT

In the column before last, there is Transaction.TxnDate 1/31/2014. Set analysis evaluates the whole data set, in this case selected data, not per table row, hence you get this result.

Re: Set analysis question about max function

Fri, 13 Jun 2014 01:22:55 GMT

Does it mean that set analysis does not respect chart dimensions?

Re: Set analysis question about max function

SergeyMak — Fri, 13 Jun 2014 01:24:39 GMT

not set analysis, but $() - $sign expansion

Re: Set analysis question about max function

Fri, 13 Jun 2014 01:42:18 GMT

so what should be set analysis?

sum({<Transactions.TxnDate={(Max(Transactions.TxnDate))}>}Amount)

This gives 0 output.

Re: Set analysis question about max function

SergeyMak — Fri, 13 Jun 2014 01:42:42 GMT

If you are trying to get the value of last day when you had sales

try this expression

Sum({$<Transactions.TxnDate={$(='"' & Date(MAX({$<Transactions.TxnDate=p({$<Amount={">0"}>})>}Transactions.TxnDate)) & '"')}>}Amount)

Re: Set analysis question about max function

SergeyMak — Fri, 13 Jun 2014 01:57:09 GMT

And another idea

You can have additional date-field (Transactions.TxnDateFltr) in your Transactions table which is equal to Transactions.TxnDate, but is not connected to calendar table and then you can use it like

sum({<Transactions.TxnDate={$(=Max(Transactions.TxnDateFltr))}>}Amount)

Re: Set analysis question about max function

Fri, 13 Jun 2014 13:49:09 GMT

Can you please let me know little bit about the usage of & operator here?

Re: Set analysis question about max function

SergeyMak — Mon, 16 Jun 2014 03:20:14 GMT

I used & for string concatenation because I needed "Date". So I concatenated

quotes and date