How to load latest month Data from Quarter QVDs SalesYYYYQ0X format

//Data load and store into QVD:

Let vQVD=’Sales’&Year(Today())&num(ceil(month(Today())/3),’00’);
SalesData:
LOAD InvoiceNumber, InvoiceLineNumber, InvoiceDate, CustomerID, SalesmanID, ProductID, Quantity,
Price, TotalPrice,’Sales’&Year(Today())&num(ceil(month(Today())/3),’00’) as QVDName
FROM Data.xlsx (ooxml, embedded labels, table is Sheet2);
STORE SalesData into $(vQVD).qvd(qvd);
EXIT SCRIPT;

//Loading latest month Data from latest QVD:

Set vFolder = ‘C:\Users\SUDHIKSHA\Desktop\Qlikview\’;

load * ,QvdTableName(‘Sales’&Year(Today())&num(ceil(month(Today())/3),’00’)&’.QVD’) from [$(File)] (qvd)
Where month(InvoiceDate)=month(Today());

Data Download this file

 

Advertisements

QlikView How To (or Tips & Tricks) Application

https://community.qlik.com/docs/DOC-5486

Qlikview Tutorial

Set Analysis – Source

Les set analysis_ENG

Set Analysis Data set-analysis-data

Data:

load *,Month(Trans_Date) as Month,
Year(Trans_Date) as Year,
Date(Monthstart(Trans_Date),’MMM-YY’) as MonthYear;
LOAD TransactionID,
Trans_Date,
TIME_KEY,
TIME_SDESC,
Product,
Brand,
MANUFACTURER_LDESC,
CATEGORY_LDESC,
Sales,
[Volume Sales]
FROM
[Set Analysis Data.xlsx]
(ooxml, embedded labels, table is Data);

https://community.qlik.com/docs/DOC-4951

https://community.qlik.com/docs/DOC-6771

Incremental Load – Qlikview

4.PNG

1.PNG

2.PNG

3.PNG

5.PNG

Download Xls file: employeedata1

 

 

EmpNo Ename Date Sales Modified_Date
E1 Gopi 1-Mar-17 10 1-Mar-17
E2 Sagar 1-Mar-17 15 1-Mar-17
E1 Gopi 2-Mar-17 25 2-Mar-17
E2 Sagar 2-Mar-17 20 2-Mar-17
E1 Gopi 3-Mar-17 45 3-Mar-17
E2 Sagar 3-Mar-17 50 3-Mar-17
E1 Gopi 3-Mar-17 45 4-Mar-17
E2 Sagar 3-Mar-17 50 4-Mar-17

 

Reference:

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

 

YTD In Qlikview

Sum({< DateField = {“>=$(=Date(YearStart(vSelectedDate)))<=$(=Date(vSelectedDate))”} >} Sales)

Sum({<Year=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

Sum({< Month,Year,Date = {‘>=$(=Date(YearStart(max(Date))))<=$(=Date(max(Date)))’} >} Sales)

YTD Average:

Sum({< Monthname,Year,Month={“>=$(=Date(YearStart(max(Month)),’YYYY MMM’))<=$(=Date(max(Month),’YYYY MMM’))”} >} Sales)
/
Count( {<Monthname,Month={“>=$(=Date(YearStart(max(Month)),’YYYY MMM’))<=$(=Date(max(Month),’YYYY MMM’))”}>}DISTINCT Month)

Monthname is in MMM format.

Capture.PNG

Excluding values in Set Analysis

Data:

ID Field Amount
1 A 1
2 B 2
3 C 1
4 X 2
5 3
6 A 4
7 B 1
8 C 2
9 X 1
10 2
11 A 3
12 B 4
13 C 1
14 X 0

Data:

LOAD ID,
Field,
Amount
FROM
ExcludingValues.xlsx
(ooxml, embedded labels, table is Sheet1);

capture

 

capture

Excluding values in Set Analysis