topic Re: Point to Point values in straight table in QlikView

Point to Point values in straight table

Thu, 28 Jan 2016 20:46:14 GMT

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-Year	QuarterId	Metric	Value
Jan-15	1	30+ %	3.1%
Feb-15	1	30+ %	4.5%
Mar-15	1	30+ %	5.6%
Apr-15	2	30+ %	2.8%
May-15	2	30+ %	4.1%
Jun-15	2	30+ %	4.2%
Jul-15	3	30+ %	6.8%
Aug-15	3	30+ %	5.3%
Sep-15	3	30+ %	3.2%
Oct-15	4	30+ %	3.1%
Nov-15	4	30+ %	4.2%
Dec-15	4	30+ %	5.5%

What I want as output from my straight table.

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

Re: Point to Point values in straight table

swuehl — Thu, 28 Jan 2016 21:07:58 GMT

Maybe something like

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

in a table with dimensions Quarter and Metric.

Re: Point to Point values in straight table

Thu, 28 Jan 2016 21:16:44 GMT

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,

Re: Point to Point values in straight table

swuehl — Thu, 28 Jan 2016 21:35:48 GMT

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?

Re: Point to Point values in straight table

Thu, 28 Jan 2016 23:40:17 GMT

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

Re: Point to Point values in straight table

settu_periasamy — Fri, 29 Jan 2016 03:30:01 GMT

May be this..?

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

Re: Point to Point values in straight table

swuehl — Fri, 29 Jan 2016 10:39:16 GMT

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.

Re: Point to Point values in straight table

Fri, 29 Jan 2016 14:38:57 GMT

Thank you. This works perfectly!