Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
That worked for me. Thank you!