AsOf table not working

[gauthamchilled]

Hi,

I created a AsOf table based on the example found in the community and linked it with my calendar table.

I dont want to use set analysis so when i click on the flag to choose from YTD, or Rolling 12 or current the data should change according to that. But it is not working.

Please find the attached example and help me out please thanks.

Gautham.

[sunny_talwar]

May be like the attached

[gauthamchilled]

Hi Sunny,

Perfect. But can you please tell me where exactly you did the changes in the dashboard to make it work or what i did wrong?

Thanks

Gautham

[sunny_talwar]

Your model was not right. It was connecting on year instead of Test Date. Secondly, you were creating monthly asof connection where as you probably needed daily connection unless your actually data is aggregated at a monthly level.

So to create the AsOfPeriodTable, this is the script I used

AsOfPeriodTable:

LOAD [Test Date] as AsOfPeriod,

  'Current' as PeriodType,

  [Test Date]

Resident Calendar;

Concatenate (AsOfPeriodTable)

//Load Rolling 12 into AsOf table

LOAD [Test Date] as AsOfPeriod,

  'Rolling 12' as PeriodType,

  Date([Test Date] + 1 - IterNo()) as [Test Date]

Resident Calendar

While IterNo() <= 365;

Concatenate (AsOfPeriodTable)

//Load YTD into AsOf table

LOAD [Test Date]  as AsOfPeriod,

  'YTD' as PeriodType,

  Date([Test Date] + 1 - IterNo()) as [Test Date]

Resident Calendar

While [Test Date] + 1 - IterNo() >= YearStart([Test Date]);

Some important changes highlighed in red above...

Also, make sure that you don't make any selections in test date. also selections to dates should be made in the asofdate. for current view similar to test date you can always use the set analysis {<PeriodType = {'Current'}>}

[gauthamchilled]

Hi Sunny,

Thanks for the explanation. But I want to make selection in Month and Year, then period type to see the data not the date. how can i achieve this?

Thanks

Gautham

[sunny_talwar]

How do you plan to visualize this information? I mean lets say you have selected year 2016 and PeriodType = Rolling 12. What time duration do you expect to see in this case?

[gauthamchilled]

Say for example, I select year 2016, period type =rolling 12 and month = Mar

Expected information is last 12 months from Mar 2016.

At any point of time i wish to select Month and Year then period type

[sunny_talwar · Accepted solution]

Check out the attached

AsOfPeriodTable:

LOAD [Test Date] as AsOfPeriod,

  Year([Test Date]) as AsOfYear,

  Month([Test Date]) as AsOfMonth,

  'Current' as PeriodType,

  [Test Date]

Resident Calendar;

Concatenate (AsOfPeriodTable)

//Load Rolling 12 into AsOf table

LOAD [Test Date] as AsOfPeriod,

  Year([Test Date]) as AsOfYear,

  Month([Test Date]) as AsOfMonth,

  'Rolling 12' as PeriodType,

  Date([Test Date] + 1 - IterNo()) as [Test Date]

Resident Calendar

While IterNo() <= 365;

Concatenate (AsOfPeriodTable)

//Load YTD into AsOf table

LOAD [Test Date]  as AsOfPeriod,

  Year([Test Date]) as AsOfYear,

  Month([Test Date]) as AsOfMonth,

  'YTD' as PeriodType,

  Date([Test Date] + 1 - IterNo()) as [Test Date]

Resident Calendar

While [Test Date] + 1 - IterNo() >= YearStart([Test Date]);

Again make selections in AsOfYear and AsOfMonth instead of Month and Year....

HTH..

I am of to sleep now... if there are other issue, I might be able to help tomorrow if no body else helps in the meantime