Chart with Calculated dimensions

[#1]

Hello Experts!

I have to create a table given in the sample below. Basically going down are different KPIs and across are the time periods. I am not sure how to create a table like this since there is no dimension in here. These KPIs are calculated based on different date fields. For example, New Orders is based on order date where as Completed Orders is based on date completed. I have about 8 more KPIs like these to add to this table. Any suggestions you can provide would be of great help.

Thanks a lot in advance!

	Current Date	MTD	Jun 2010 (Prior Month)	Jul 2009 (Prior Year Month)
New Orders	1,500	25,000	26,000	24,000
% Order Completed	80%	70%	89%	75%
Completed Orders	1,000	20,000	21,000	19,000

[#2]

My suggestion would be

1] To create a seperate date island not associated with any dates in you table. (autogenerate the dates based on the min date and max date in your table

2] Your current date, MTD... will be dimensions where you will use the dates from your date island

3] New orders, % completed will be expressions where you will use the if statement to compare dates with the date island date field

If you provide a sample data, it would be easier to demonstrate.

Nimish

[#3]

Thank you for your response, Nimish!

I do not speak 'QlikView' well, so please pardon my language. Its been only two weeks I am experimenting with this tool.

Here is how we have the setup:

1. We read data from Order and Order Detail tables and add some flags in the QVW load script.

2. Currently different KPI is displayed as a Text box based on filter on related flag and the text boxes are grouped together based on time period - Current Date, MTD.

I am trying to convert this to a table format mentioned above for simpler look.

I have attached scrubbed test data with few fields. Hope this gives you an idea of what information I am working with. Please let me know if you need any other details.

If you can explain the Date island concept with reagrds to my test data and sample report, that would be great!

Really appreciate your help!

Thanks!

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/0677.KPITable_2D00_testdata.xlsx:550:0]

[#4]

Forgot to mention, open orders is count of distinct order_cde when the open order flag is 1. Similarly completed orders count is based on completed order flags from the file. These are set as expressions for the textboxes per current design.

Thanks!

[#5 · Accepted solution]

I have uploaded a sample file. It has the source excel file and the QV file.

Let me know if this is what you are looking for since I have made some assumptions while preparing the sample file.

Also, the method used in not very efficient but I have no idea what other way thee could be.

Nimish

[#6]

Nimish, Thanks a lot for your detailed response! I think this gives me an idea for getting the table I was looking for.

Thanks!