Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Set analysis -variable usage-for date selection.

Hi Experts,

I need to show a pivot table chart which should appear as below.

ChannelProcess NameSales
EmailProcess ABC15 M
FacebookProcess XYZ25 M
Postal MailProcess QWE5 M

I have source file in the form of two tables.

Table 1 appears as follows:

Order KeyOrder DateSales
11/2/201512 M
22/4/20152 M
32/6/20150.5 M

Table 2 appears as follows:

Process KeyProcess NameProcess Start DateProcess End Date
1 PProcess ABC1/1/201528/2/2015
2 PProcess XYZ1/4/201524/4/2015
3 PProcess QWE1/6/201527/6/2015

In my requirement, there is a necessity to keep both the calendars independent.

Now, I need to select the order dates of Table1 which falls under the corresponding process date range in the Table 2. Finally I need to show the respective total sales.

In expression we have something like below.

=Sum( {<$(Variable1),$(Variable2),OrderKey=p({<$(=vProcesssDateCheck)>})>} Sales)

where vProcesssDateCheck='[Order Date]={">=$(=([Process Start Date]))<=$(=([Process End Date]))"}'

It works fine when a particular Process Name is selected. But as a whole the Process date Range is not filtered by the variable for each record, showing '0' for Sales.

Any help would be much appreciated.



2 Replies

Re: Set analysis -variable usage-for date selection.

You are trying to do a row by row calculation. This cannot easily be done using set expressions, as they are evaluated once for the chart and not once per row.

I suggest you read this article which will explain how you can do what you require: Canonical Date

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Valued Contributor II

Re: Set analysis -variable usage-for date selection.

see the attachment.. it might be helpfull

Community Browser