Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ananyaghosh
Creator III
Creator III

need 'As Of Table' help

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

5 Replies
sunny_talwar

What is the issue that you are running into?

ananyaghosh
Creator III
Creator III
Author

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

ananyaghosh
Creator III
Creator III
Author

Hi,

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

ananyaghosh
Creator III
Creator III
Author

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.

ananyaghosh
Creator III
Creator III
Author

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 ?