topic need 'As Of Table' help in QlikView

need 'As Of Table' help

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

Hi,

I am using below script for creating as of table:

AsOfTable:
LOAD "Process Date" as AsOfProcessDate,
"Process Date",
'Current Date' as FlagDate,
"Month ID" as AsOfProcessMonth,
"Month ID",
'Current Month' as FlagMonth
Resident [Dim Date];

Concatenate (AsOfTable)
LOAD
"Process Date" as AsOfProcessDate,
Date("Process Date" - 1) as [Process Date],
'Previous Date' as FlagDate,
"Month ID" as AsOfProcessMonth,
"Previous Month ID" as "Month ID",
'Previous Month' as FlagMonth
Resident [Dim Date];

[As-Of Calendar]:
Load
AsOfProcessDate,
AsOfProcessMonth,
FlagDate,
FlagMonth
//Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
//Year(AsOfMonth)-Year(Month) as YearDiff
Resident AsOfTable;
// Where AsOfMonth >= Month;

Drop Table AsOfTable;

now I attached the QVW file. Please tell what is wrong here

Re: need 'As Of Table' help

sunny_talwar — Fri, 28 Apr 2017 11:25:58 GMT

What is the issue that you are running into?

Re: need 'As Of Table' help

ananyaghosh — Fri, 28 Apr 2017 11:45:35 GMT

Hi,

The issues are:

1. it is generating the synthetic key with Dim_date table.

2. I am using the following Set analysis for calculating current and previous day sales:

Sum({<FlagDate = {'Previous Month'}>}[Sales Posting Amount USD])

and

Sum({<FlagDate = {'Current Month'}>}[Sales Posting Amount USD])

and they show the same results.

3. Also I need to show previous week's sales and current week's, Rolling periods of 9 Months sales. So for that purposes how can I prepare my data model script?

Thanks,

Sandip

Re: need 'As Of Table' help

ananyaghosh — Sat, 29 Apr 2017 04:47:59 GMT

Hi,

Can you help me please for preparing the as of table?

Re: need 'As Of Table' help

ananyaghosh — Sat, 29 Apr 2017 05:43:18 GMT

Hi,

Now I am using this script:

AsOfTable:
LOAD
[Order Date] as AsOfOrderDate,
[Order Date],
'Current Date' as FlagDate
Resident [Order Date Data];

Concatenate (AsOfTable)

LOAD
[Order Date] as AsOfOrderDate,
Date([Order Date] - 1) as [Order Date],
'Previous Date' as FlagDate

Resident [Order Date Data];

AsOfTableMonth:
LOAD
[Month ID] as AsOfMonthID,
[Month ID],
'Current Month' as FlagMonth
Resident [Order Date Data];

Concatenate (AsOfTableMonth)

LOAD
[Month ID] as AsOfMonthID,
date(addmonths([Order Date],-1),'YYYY-MM') as [Month ID],
'Previous Month' as FlagMonth
Resident [Order Date Data];

and Previous Date Calculations work well, but previous month is not working.

Re: need 'As Of Table' help

ananyaghosh — Sun, 30 Apr 2017 08:11:46 GMT

Hi,

I am preparing a Qlikview document. Can you help me for previous month also?

Also Can I add together previous month, previous date's and week's calculation in one as of table ?