Help charting with timeline

joefeyas — Wed, 16 Mar 2011 14:09:25 GMT

Hi all,

I am having some difficulty with a a stacked bar chart using fiscal year with drill down in to fiscal quarter and fiscal month.

First thing is to limit the bars on the chart dynamically to AddYears(Today(),-1) through AddYears(Today(), +5). I was able to get this working using IF statements, but I thought there maybe was a better (faster/optimal) way?

My Fact table looks something like this:

Date1
Date2
Date3
Date4
...
DateX
Flag1
...
FlagX
Category
Sale_Amnt1
Sale_Amnt2

The chart should be Sale_Amnt stacked by category where flag =1 and Date1 < Date2 and Date1 is in the display range (2010-2016). For Example:

Date1 = 3/3/2011
Date2 = 3/3/2013
SaleAmt = $10
Category = A

Then $10 should show up for each bar 2011, 2012, 2013 stacked in the "A" segment

I have it set up now so my Dimension is Date1 and have a feeling this is incorrect...

My Expressions are nested IF statments, e.g.:

IF(flag=1 and Date1 < Date2, IF(Category ='A', IF(SomeotherCondition, SaleAmnt1, Saleamnt2)))

I also have built a master calendar of fiscal dates 1995 through 2020. But because there are many date fields in my fact table I was not sure how to link the calendar to the fact table.

Currently the chart is very slow and the data is coming out incorrectly bucketed.

Any help is appreciated

topic Help charting with timeline in QlikView

Help charting with timeline