Qlik Adhoc Reporting

[Halesha_Bandri]

Step1:

Create two inline load as like below. ( one  for Measures list and another one for Dimensions list )

DimensionList:
LOAD * INLINE
[Dimensions
Year
Quarter
Month
Week
Region
Affiliate
Brand
Therapy Area
HCP Speciality
HCP Type
Email Subject
Template Name
Fragment Name
];

MeasureList:
LOAD * INLINE
[Measures
#Sent
#Delivered
#Opened
#Clicked
#Bounced
#Unsubscribed
#Unique HCPs
%Open Rate
%Click Rate
%CTOR
%Bounce Rate
%Unsubscribe Rate
];

Step2:

2.1 : Add Dimension Filter with the following expression

= Aggr(Only({1<Dimensions = P({1<Dimensions ={'Year','Quarter','Month','Week','Region','Affiliate','Brand','Therapy Area','HCP Speciality','HCP Type','Email Subject','Template Name','Fragment Name'}>})>} Dimensions),Dimensions)

2.2 : Add Measure Filter with the following expression

= Aggr(Only({1<Measures = P({1<Measures ={'#Sent','#Delivered','#Opened','#Clicked','#Bounced','#Unsubscribed','#Unique HCPs','%Open Rate','%Click Rate','%CTOR','%Bounce Rate','%Unsubscribe Rate'}>})>} Measures),Measures)

Step3: Add a Table with all the required dimensions and measures.

In Show Column If, we need to provide conditions as like below.

For Dimensions : 

1.  For Year Dimension , the Expression =  Match('Year','$(=Concat(Dimensions,Chr(39)&','&Chr(39)))')

2.  For Quarter Dimension , the Expression =  Match('Quarter','$(=Concat(Dimensions,Chr(39)&','&Chr(39)))')

For Measures :

1.  For #Sent  measure  , the Expression =  Match('#Sent','$(=Concat(Measures,Chr(39)&','&Chr(39)))')

2.  For #Delivered measure , the Expression =  Match('#Delivered','$(=Concat(Measures,Chr(39)&','&Chr(39)))')

Step:4

Based on Dimension filter selection and Measure filter selection, straight table will show result according to that.

By default: It will show all measures/Dimensions. which can be restricted in straight table as like below.

Go to ==> Data Handling ==> Calculation Condition ==> add expression as like below

GetSelectedCount(Dimensions)>=1 AND GetSelectedCount(Measures)>=1

Go to ==> Data Handling ==> Display Message  ==> add message  as like below

='Please select at least one measure and one dimension in-order to view the Report'