Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
lotitolo
New Contributor II

Display only the last date in a table pivot

Hello,

I would like to know what the procedure is to display only the last month of my pivot table dynamically.

Indeed when I use the filter function I lose my evolution calculations from the current month to the previous month (and in addition to that, it applies to the other calculation of my sheet, which I don't want)


Here is a small picture to illustrate the situation:Capture.PNG

Ths,

 

Lotitolo

Labels (3)
7 Replies
Vegar
Honored Contributor

Re: Display only the last date in a table pivot

You could do this by using SET analysis in your expression.
I assume you have an expression like this
SUM( SomeField)/ sum(somethingElse)

Change it to
SUM({< Date = {"$(= max(Date))"}>} SomeField)/ sum({< Date = {"$(= max(Date))"}>} somethingElse)
Please ekskuse my Norglish and Swenglish typos.
lotitolo
New Contributor II

Re: Display only the last date in a table pivot

Wow, thank you.

It works perfectly in terms of the amount:

SUM({< Date = {"$(= max(Date))"}>>} [Spending Total])/1000

But, where it gets complicated is when I want to compare the value of the current month to the previous month:

SUM({< Date = {"$(= max(Date))"}>>} [Spending Total])/Before(Sum({<month>}[Spending Total])) - 1) * Avg(1)

The formula is good, but I have no value displayed.

Vegar
Honored Contributor

Re: Display only the last date in a table pivot

This could be solved with set as well. Try something like this.

SUM({< Date = {"$(= max(Date))"}>} [Spending Total])/Before(Sum({<Date = {"$(= addmonths(max(Date)),-1)"}>}[Spending Total])) - 1) * Avg(1)
Please ekskuse my Norglish and Swenglish typos.
lotitolo
New Contributor II

Re: Display only the last date in a table pivot

**bleep**, it's impossible to operate the function after the slash "/".
UM({< Date = {"$(= max(Date))"}>>} [Spending Total])/Before(Sum({<Date = {"$(= addmonths(max(Date)),-1)"}>}[Spending Total])) - 1) * Avg(1)

I tried the exercise with a little bit  simpler function (have the % on the total of the column):
SUM({< Date = {"$(= max(Date))"}>>} / sum(TOTAL < Date = {"$(= max(Date))"}>>[Spending Total])

Nothing matters... however the code seems logical to me.

Vegar
Honored Contributor

Re: Display only the last date in a table pivot

I think I got the parenthesis wrong (I where posting from by cell phone).
Try the adjusted expression below.

SUM({&lt; Date = {"$(= max(Date))"}&gt;} [Spending Total])/(Sum({<DATE>}[Spending Total])) - 1) * Avg(1)</DATE>
Please ekskuse my Norglish and Swenglish typos.
Vegar
Honored Contributor

Re: Display only the last date in a table pivot

Something went wrong with the formatting.
SUM({< Date = {"$(= max(Date))"}>} [Spending Total])
/
SUM({<Date = {"$(= addmonths(max(Date),-1))"}>}[Spending Total])
Please ekskuse my Norglish and Swenglish typos.
lotitolo
New Contributor II

Re: Display only the last date in a table pivot

I found out why it didn't work for the last measure.
Because for the first measure I only want to display data from the last month. And when I do that, I don't have previous month's sales.
So it is impossible to compare month N with month N-1.
It's painful this display management ^^

 

Capture.PNG