no relation betwwen dimension and fact but how will i join it

[Anonymous]

I have a dimension table .it has just 3 rows and single column like 2012,2011,

Last 12 Months(for last 12 mm - last12mm meanings 2012sep to 2011oct).

i have detail table which contains columns like

cy_jan,cy_feb....cy_sep,cy_total(cy all total)

ly_jan-ly_dec,ly_total(ly all total)   etc........

i want to know

if anybody click on 2012 it will display list of columns cy_jan---to -cy_sep

                                      click on 2011 it will display ly_jan-------to--ly_dec(in respect of 2011)

                                      click on Last 12 Months it will display cy_sep-------to--ly_oct

is it possible in qlikview?

please find the attached file which contains sample data.

i have posted it earlier but did not get any response.

please let me know whether it is possible using qlik view.

[johnw]

I recommend converting all of your year/month columns into rows, possibly with a crosstable load.  You want your detail table to contain detail for each month as rows in the table.  I'd use dates formatted to just show the year/month, so when you see me use a year/month below, realize that it's not a text field but the equivalent date.  All that said, you could then build the dimension table like this:

Date Range, Month
2011, 2011 Jan
2011, 2011 Feb
...
2011, 2011 Dec
2012, 2012 Jan
2012, 2012 Feb
...
2012, 2012 Dec
Last 12 Months, 2012 Sep
Last 12 Months, 2012 Aug
...
Last 12 Months, 2011 Oct

That done, your tables now link up, and when you select the dimension value, it automatically brings in all the correct months of data.

[Anonymous]

HI JOHN,

i am new in QV.

trust me i have tried my level best but i am unabled  convert your suggestion.

can you help me more?

[#4]

Hi,

Please see attached file. Hope it will give you an idea.

Regards,

Janzen

[#5]

Hi,

Here is another example how this data model can be changed and used more effectively. I have cross tabled your data and crouped all your Time fields as OriginalTime to facilitate better calculations. Beyond which i have used the inline table from the above post to give you Year.

Hopefully this should help

Regards

Rahul

[johnw]

anutosh_dhar wrote:

i am new in QV.

trust me i have tried my level best but i am unabled  convert your suggestion.

can you help me more?

Here's an example.  It uses actual dates for your months, which I strongly recommend.  There's probably a more efficient way to do some of these steps, but I don't want to take any more time to clean it up.  The final data model is the main thing I'm trying to demonstrate, and most specifically the table shown on the left on the screen.