Calculating Trends across multiple expressions in the same chart

[#1]

Hi All -

A competing BI tool in house can do a chart like this -

Select Period: rolling last 7 day of date

Selected Period	Last 1-7 days	Last 8-14 days	Trend up/Down
KPI 1	X	y	Up
KPI 2	A	B	Down
KPI 3	C	D	Up
KPI 4	E	F	Down

The data might look like:

Date, Dimension 1, Dimension 2, Fact 1, Fact 2, Fact 3

, where the KPI 1 - KPI 4 above are calculations off of the Facts. For illustration purposes,

KPI1 = sum(Fact 1),

KPI2 = sum(Fact2)

KPI3 = sum(Fact1)/sum(Fact 3)

KPI4 = sum(Fact 2)/sum(Fact 3)

Is this possible? What concerns me is the "Selected Period" is derived from the "Date" to form the dimension, but the Trend Up/Down line looks across the two KPI calculations and it not really a dimension.

Thanks for any help,

Chris

[pover · Accepted solution]

Chris,

You're going to have to look at a couple different functions and techniques to do this report.

First to make you can either make a table that has a list of the KPI's and in the expression use a if or pick to choose the expression according to the value in the KPI column like:

if(KPI=1,sum(Fact 1), if(KPI=2,sum(Fast2)...
or
pick(KPI,sum(Fact 1),sum(Fact 2)...)

or you can take a look at the valuelist(pick(match()) funtions to make the report in the below post.

http://community.qlik.com/forums/t/25547.aspx

For the data range use set analysis in each expression (Last 1-7 days, Last 8-14 days) like the following post:

http://community.qlik.com/forums/t/25547.aspx

And finally for the Trend Up/Down just use something like the following:

If([Last 1-7 days]>[Last 8-14 days],'Up','Down')

The [Last 1-7 days] and [Last 8-14 days] refer to the results of the column and you can use the function column(1) and column(2) in their place.

I hope that beats the competing BI tool.

Regards.

[#3]

That worked for me. Thank you!