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: 
Not applicable

Point to Point values in straight table

I am trying to get point to point values in a straight table when the dimension is set to Quarter. I do not want to aggregate the values for all three months in each quarter. Instead, I want to display the value from the last month in each quarter. My dimension is Quarter_Year so If I have 2014 - 2015 chosen the table will display 8 columns. I have tried to use Max() but then it will only give me the metric for the selected period, not for each Quarter-Year selected. Thanks in advance!


Data Table example

  

Month-YearQuarterIdMetricValue
Jan-15130+ %3.1%
Feb-15130+ %4.5%
Mar-15130+ %5.6%
Apr-15230+ %2.8%
May-15230+ %4.1%
Jun-15230+ %4.2%
Jul-15330+ %6.8%
Aug-15330+ %5.3%
Sep-15330+ %3.2%
Oct-15430+ %3.1%
Nov-15430+ %4.2%
Dec-15430+ %5.5%

What I want as output from my straight table.

 

Q-1Q-2Q-3Q-4
30+ %5.6%4.2%3.2%5.5%
1 Solution

Accepted Solutions
swuehl
MVP
MVP

You just need to create MonthYear as a dual value, see

Data Types in QlikView

Why don’t my dates work?

Then MonthYear will have an underlying numeric value, which you can use in FirstSortedValue() sort weight parameter

=FirstSortedValue( {<Metric={'30+%'}>} Value, -MonthYear)

See attached.

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe something like

=FirstSortedValue( Value, -[Month-Year] )

in a table with dimensions Quarter and Metric.

Not applicable
Author

I'm not sure that would work with my case. I cannot change the dimensions because they are conditional based on a selection from a listbox.

List Box

Month

Quarter

Year

The straight table has twenty metrics based on the Values field. My issue is that some metrics require sums across Quarter and some require Point to Point, The sums are easy, but I'm having issues with the point to point. So I don't think I can sort, due to multiple metrics in Values and I cannot change the dimensions,

swuehl
MVP
MVP

You can also use the expression with other dimensions, it will always return the latest Value per available Month-Year value.

Not sure if I understand your setting though, could you upload a small sample QVW and maybe some expected results under different scenarios?

Not applicable
Author

Here is an example of the table I'm trying to create.

settu_periasamy
Master III
Master III

May be this..?

=Max({<Metric={'30+%'}>}Value)

swuehl
MVP
MVP

You just need to create MonthYear as a dual value, see

Data Types in QlikView

Why don’t my dates work?

Then MonthYear will have an underlying numeric value, which you can use in FirstSortedValue() sort weight parameter

=FirstSortedValue( {<Metric={'30+%'}>} Value, -MonthYear)

See attached.

Not applicable
Author

Thank you. This works perfectly!