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

Dates in Set Analysis

Set vLastMonth= “=Date(Max(Month),’MMM-YY’)”;
Set v2ndLastMonth= “=Date(AddMonths(Max(Month),-1),’MMM-YY’)”;
Data:
LOAD*, Date(MonthStart(Date),’MMM-YY’) as Month,Year(Date) as Year;
LOAD CategoryName,
Date,
Sales,
Amount
FROM
Data.xlsx
(ooxml, embedded labels, table is [Data (2)]);

Capture.PNG

 

Expressions:

Sum( {$<Month={“$(=Date(Max(Month),’MMM-YY’))”}>} Amount )

Sum( {$<Month={“$(=Date(AddMonths(Max(Month),-1),’MMM-YY’))”}>} Amount )

Capture.PNG

Capture.PNG

Dates in Set Analysis

How to write the below set analysis in different way?

Sum({$<CurDay -={“$(=Date(Date#(‘5/30/2016′,’MM/DD/YYYY’),’MM/DD/YYYY’))”}>}[Causal Total Time])

If(CurDay=MakeDate(2016,5,30),0,1) as IsIncluded,

set analysis expression:

Sum({$<IsIncluded ={1}>} [Causal Total Time] )