custom label based on variable

[#1]

Hi,

Please excuse if I am using basic techniques but I am an accountant not a programmer.

I am creating a report which melds historical data, current stock on hand and forecast units for future months to analyse our stock forecasts.

I have created a variable called vMaxPeriod where and have assigned every month a number in a inline list, e.g.

Month, MonthNum

Sep13,3

Oct13,4

Nov13,5

The vMaxPeriod then uses a formula to determine the month with the most recent data.

I am using a straight table with up to 12 conditionally shown columns that represent the previous 12 months before the most recent month. As time progresses these 12 months will roll forward so I have used the formula sum(if(MonthNum=vMaxPeriod-1) to get the previous month data, sum(if(MonthNum=vMaxPeriod-2) to get 2 months ago data etc

I have created another variable called vNoOfMonths which allows the user to set the number of months previous to the current month they want to see up to 12.

As the columns on the straight table are conditional, based on the number in the vNoOfMonths variable, only the required number of columns appear. E.g. if the current month is June14 (its MonthNum is 12) and if the vNoOfMonths is set to 5 then the columns for Feb to Jun only appear as the formula e.g. sum(if(MonthNum= vMaxPeriod-1,Units)) would give the units sold in may, sum(if(MonthNum= vMaxPeriod-2,Units)) would show April etc.

This all works fine but I want to be able to create labels for each of the straight tables colums. I have got as far as saying vMaxPeriod-1 and get 11 as the label but I want to be able to have Qlikview then be able to look up 11 as the MonthNum and replace this with May14.

Is there a way to do this?

In the attached screen shot SOH is always the stock on hand as at the day the report is being viewed. I have select 5 months to show at the top, as qlikview as determined that the current month number is 12 it will display the months with numbers 7-11. This is what is being displayed in the label but I want to replace it with Jan14-May14. I am also continuing that numbers so Jul14 will be 13 etc

Thanks

David

[eduardo_sommer]

You can create, for each date in your data, a dual field where the text part has the YearMonth (May14, Apr14, etc) and the numeric part is calculated as (Year-1)*12 + Month. Using this, May14, will have a number 24161, Apr14 will have 24160, and so on. When you define your control variable as being Jun14, you will store there the number 24163  (2013*12+60) For the label, you use the text part of the dual field.

If you can share your qvw, I can help you in the creation of this fields.

Eduardo

[#3]

Here is the .qvw. It is only an hour old so there isnt much to it yet but I need to fix this label issue.

where the column label says 11 it should say May-14 which has the period number 11411. I cannot use the 11411 as a reference as when we move into FY15 (July is 11501) I still need it to add months from the end of FY14

[#4]

This idea is a little above my level of expertise but if you can illustrate it on the qvw I might be able to understand

Thanks

David