ValueList - Strange

mikru93_ — Wed, 07 Mar 2018 09:47:35 GMT

Hi,

For a KPI-tracking I wanted to create a calculated dimension to show results for the last 5 Years and the actual Year, last 3 Quarters and actual Quarter and last 3 Months and actual Month all in one graph.

To achieve this I created a ValueList:

=ValueList(

v.FifeYearBefore

,v.FourYearBefore

,v.ThreeYearBefore

,v.TwoYearBefore

,v.OneYearBefore

,v.ActualYear

,v.ThreeQuarterBefore

,v.TwoQuarterBefore

,v.OneQuarterBefore

,v.ActualQuarter

,v.ThreeMonthBefore

,v.TwoMonthBefore

,v.OneMonthBefore

,v.ActualMonth

)

v.FifeYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-5)),'1','1'),'YYYY')

v.FourYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-4)),'1','1'),'YYYY')

v.ThreeYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-3)),'1','1'),'YYYY')

v.TwoYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-2)),'1','1'),'YYYY')

v.OneYearBefore = date(MakeDate(Year(AddYears(max([Effective Date]),-1)),'1','1'),'YYYY')

v.ActualYear = date(MakeDate(Year(max([Effective Date])),1,1),'YYYY')

v.ThreeQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-9)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-9))/3)

v.TwoQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-6)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-6))/3)

v.OneQuarterBefore = date(MakeDate(Year(addmonths(QuarterStart(max([Effective Date])),-3)),'1','1'),'YYYY')&'-Q' & ceil(month(AddMonths(QuarterStart(max([Effective Date])),-3))/3)

v.ActualQuarter = date(MakeDate(Year(QuarterStart(max([Effective Date]))),1,1),'YYYY')&'-Q'&ceil(month(QuarterStart(max([Effective Date])))/3)

v.ThreeMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-3),'YYYY-MMM')

v.TwoMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-2),'YYYY-MMM')

v.OneMonthBefore = date(AddMonths(MonthStart(max([Effective Date])),-1),'YYYY-MMM')

v.ActualMonth = date(MonthStart(max([Effective Date])),'YYYY-MMM')

I checked all Variables and they are working but somehow in the Dimension the 'YYYY-MMM' nevers shows anything with January.

Everything else is working flawlessly.

Does anyone have experience with similar problems?

Thanks for your help.

topic ValueList - Strange in QlikView

ValueList - Strange