Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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%
Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Point to Point values in straight table

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.

7 Replies
MVP
MVP

Re: Point to Point values in straight table

Maybe something like

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

in a table with dimensions Quarter and Metric.

Not applicable

Re: Point to Point values in straight table

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,

MVP
MVP

Re: Point to Point values in straight table

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

Re: Point to Point values in straight table

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

Re: Point to Point values in straight table

May be this..?

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

MVP
MVP

Re: Point to Point values in straight table

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

Re: Point to Point values in straight table

Thank you. This works perfectly!

Community Browser