Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

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
Highlighted

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

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())

Highlighted
Contributor III
Contributor III

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

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!

Highlighted

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

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())

Highlighted
Contributor III
Contributor III

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

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.

Highlighted

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

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

Highlighted
Contributor III
Contributor III

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

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

Highlighted

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

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

Highlighted
Contributor III
Contributor III

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

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