topic Re: AsOf table not working in QlikView

AsOf table not working

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: AsOf table not working

sunny_talwar — Tue, 06 Dec 2016 02:40:00 GMT

May be like the attached

Re: AsOf table not working

gauthamchilled — Tue, 06 Dec 2016 02:49:48 GMT

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

Re: AsOf table not working

sunny_talwar — Tue, 06 Dec 2016 02:54:25 GMT

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'}>}

Re: AsOf table not working

gauthamchilled — Tue, 06 Dec 2016 03:24:04 GMT

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

Re: AsOf table not working

sunny_talwar — Tue, 06 Dec 2016 03:30:12 GMT

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?

Re: AsOf table not working

gauthamchilled — Tue, 06 Dec 2016 03:36:26 GMT

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

Re: AsOf table not working

sunny_talwar — Tue, 06 Dec 2016 03:44:55 GMT

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