Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis question about max function

HI,

I got one pivot table which looks like this

Screen Shot 2014-06-12 at 4.44.48 PM.png

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

9 Replies
SergeyMak
Partner
Partner

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"

Regards,
Sergey
mov
Champion III
Champion III

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.

Not applicable
Author

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

SergeyMak
Partner
Partner

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

Regards,
Sergey
Not applicable
Author

so what should be set analysis?

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

This gives 0 output.

SergeyMak
Partner
Partner

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)

Regards,
Sergey
SergeyMak
Partner
Partner

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)

Regards,
Sergey
Not applicable
Author

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

SergeyMak
Partner
Partner

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

quotes and date

Regards,
Sergey