Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stephanies
Contributor III
Contributor III

How to produce a table displaying the peak month of a year per dim value?

Hello,

I would like to know if the following is possible, and if yes, how?

I have a set of data from which I am looking to ascertain the peak month for transactions by merchant in a given year. In a pivot table, the data might look like so:

Merchant

JanFebMar
A152535
B10255
C301020

These are all values for 2017 (filtered). There would be 12 months of data here and thousands of merchants.

The transaction values are created with the expression Sum([Transactions]).

I would like to know if there is some way of displaying the maximum value for each merchant across the months, either by 1) displaying a table with three columns: merchant, month containing the maximum value (Peak Month) and the value itself or 2) displaying the above pivot with the maximum values highlighted:

Example Option 1
MerchantPeak MonthMax Value
AMar35
BFeb25
CJan30
Example Option 2
MerchantJanFebMar
A152535

B

10255
C301020

I cannot edit the data load.

Any help would be greatly appreciated - even confirmation that this cannot be achieved without editing the data load!

Steph

Message was edited by: Stephanie

1 Solution

Accepted Solutions
sunny_talwar

This was my bad, can you try this

FirstSortedValue(Month, -Aggr(Sum(Transactions), Month, Merchant))

Max(Aggr(Sum(Transactions), Month, Merchant))

and

If(Max(TOTAL <Merchant> Aggr(Sum(Transactions), Month, Merchant)) = Sum(Transactions), Yellow())

View solution in original post

8 Replies
sunny_talwar

May be this for 1st example chart

Dimension

Merchant

Expression

FirstSortedValue(Month, -Value)

Max(Value)

For the second example chart, you can use a background color expression like this

If(Max(TOTAL <Merchant> Value) = Value, Yellow())

stephanies
Contributor III
Contributor III
Author

Hi Sunny,

Thank you, but I'm afraid I missed out an important bit of information -  the Value is Sum([Transactions]). I.e. to derive my original pivot table, the Row would be 'Merchant', the Column 'Month' and the Measure Sum([Transactions]).

This prevents me from nesting into the FirstSortedValue expression in the first table.

I'll edit my question to reflect this!

sunny_talwar

Try this

FirstSortedValue(Month, -Aggr(Sum(Transactions), Month))

Max(Aggr(Sum(Transactions), Month))

For the other one

If(Max(TOTAL <Merchant> Aggr(Sum(Transactions), Month)) = Sum(Transactions), Yellow())

stephanies
Contributor III
Contributor III
Author

Sorry Sunny. No luck with either of those, I'm afraid. The first returns a column containing the three months with the most transactions and another with the sum of those transactions. The second doesn't appear to colour anything.

sunny_talwar

Can you share an image of what you get and explain what you intend to get?

stephanies
Contributor III
Contributor III
Author

Hi Sunny,

Incorrect Table.png

This is the table I get using your expressions (anonymised). I have a column of all the merchants, but the peak month column shows only those months which have the highest max value in total for one year (regardless of merchant). All other rows besides the first two are blank.

What I intend to get is a Merchant column displaying all merchants, a Peak Month column displaying the month in a given year (2017) in which the merchant saw the most transactions, and a Max Value, which displays the sum of transactions for that merchant in their peak month.

So in my first example in my question (the blue headed table), Merchant A saw the most transactions in March, so I would want to display Merchant A / March / 35.

With that in mind, I seem to be having some success with HRank in a pivot table, but it's still not quite what I'm after:

Row:

Merchant

Column:

Month

Measures:

Sum([Transactions])

Hrank(sum([Transactions]),4,1)

This creates something like this:

Possible Solution.png

sunny_talwar

This was my bad, can you try this

FirstSortedValue(Month, -Aggr(Sum(Transactions), Month, Merchant))

Max(Aggr(Sum(Transactions), Month, Merchant))

and

If(Max(TOTAL <Merchant> Aggr(Sum(Transactions), Month, Merchant)) = Sum(Transactions), Yellow())

stephanies
Contributor III
Contributor III
Author

You've cracked it! Thank you so much, Sunny.