Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I am new to Qlikview
How to write set analysis to sum of Sales for 90 days?
Plz help.
Hi,
Please try like this.
Data:
LOAD date(date#(SalesDate,'MM/DD/YYYY')) as SalesDate,Sales INLINE [
SalesDate,Sales
10/3/2018,1
10/2/2018,2
10/1/2018,3
9/30/2018,1
9/29/2018,2
9/28/2018,3
9/27/2018,1
9/26/2018,2
9/25/2018,3
9/24/2018,1
9/23/2018,2
9/22/2018,3
9/21/2018,2
9/20/2018,2
9/19/2018,2
9/18/2018,3
9/17/2018,3
9/16/2018,3
9/15/2018,3
9/14/2018,3
9/13/2018,3
9/12/2018,3
9/11/2018,3
9/10/2018,3
9/9/2018,3
9/8/2018,3
9/7/2018,3
9/6/2018,3
9/5/2018,3
9/4/2018,3
9/3/2018,3
9/2/2018,3
9/1/2018,3
8/31/2018,4
8/30/2018,4
8/29/2018,4
8/28/2018,4
8/27/2018,4
8/26/2018,4
8/25/2018,4
8/24/2018,4
8/23/2018,4
8/22/2018,4
8/21/2018,4
8/20/2018,4
8/19/2018,4
8/18/2018,4
8/17/2018,4
8/16/2018,4
8/15/2018,4
8/14/2018,4
8/13/2018,5
8/12/2018,5
8/11/2018,5
8/10/2018,5
8/9/2018,5
8/8/2018,5
8/7/2018,5
8/6/2018,5
8/5/2018,5
8/4/2018,5
8/3/2018,5
8/2/2018,5
8/1/2018,5
7/31/2018,5
7/30/2018,5
7/29/2018,5
7/28/2018,5
7/27/2018,5
7/26/2018,6
7/25/2018,6
7/24/2018,6
7/23/2018,6
7/22/2018,6
7/21/2018,6
7/20/2018,6
7/19/2018,6
7/18/2018,6
7/17/2018,6
7/16/2018,6
7/15/2018,6
7/14/2018,6
7/13/2018,6
7/12/2018,6
7/11/2018,6
7/10/2018,6
7/9/2018,7
7/8/2018,7
7/7/2018,7
7/6/2018,7
7/5/2018,7
7/4/2018,7
7/3/2018,7
7/2/2018,7
7/1/2018,7
6/30/2018,7
6/29/2018,7
6/28/2018,7
6/27/2018,7
];
Expression:
Sum({<SalesDate={">$(=Date(Max(SalesDate)-90)) <=$(=Date(Max(SalesDate)))"}>}Sales)
Sum({<Date={">$(=Date(Max(Date)-90)) <=$(=Date(Max(Date)))"}>}Sales)
The above example says to sum sales for max(Date) to max(Date)-90 days. Now based on your date range you have to customize the expression.
Hi,
you didn't put any details on your question (what is your date column you have on your model, it's format, do you want to use expressions inside set analysis, do you use variables...)
here is some examples:
suppose that you're going to use a date field (called Date) with a format like this: DD/MM/YYYY
Static example (from 1st july to 30 september):
sum({<Date={"$(= '>=' & '01/07/2018' & '<=' & '30/09/2018')"}>} Field)
Dynamic example (last 90 days):
=Sum({$< Date = {">=$(=Date(Today()-90, 'DD/MM/YYYY'))"}>} Field)
hope it is clear
It is Not working
this my sample Data
Data:
LOAD * INLINE [
Name,Date,Sales
Gopi,2-Oct-2018,1
Ramesh,1-Oct-2018,2
John Saida,30-Sep-2018,3
Gopi,29-Sep-2018,1
Ramesh,28-Sep-2018,2
John Saida,27-Sep-2018,3
Gopi,26-Sep-2018,4
Ramesh,25-Sep-2018,5
John Saida,24-Sep-2018,1
Gopi,23-Sep-2018,2
Ramesh,22-Sep-2018,3
John Saida,21-Sep-2018,1
Gopi,20-Sep-2018,2
Ramesh,19-Sep-2018,3
John Saida,18-Sep-2018,4
Gopi,17-Sep-2018,5
Ramesh,16-Sep-2018,1
John Saida,15-Sep-2018,2
Gopi,14-Sep-2018,3
Ramesh,13-Sep-2018,1
John Saida,12-Sep-2018,2
Gopi,11-Sep-2018,3
Ramesh,10-Sep-2018,4
John Saida,9-Sep-2018,5
Gopi,8-Sep-2018,3
Ramesh,7-Sep-2018,3
John Saida,6-Sep-2018,3
Gopi,5-Sep-2018,3
Ramesh,4-Sep-2018,3
John Saida,3-Sep-2018,4
Gopi,2-Sep-2018,4
Ramesh,1-Sep-2018,4
John Saida,31-Aug-2018,4
Gopi,30-Aug-2018,4
Ramesh,29-Aug-2018,4
John Saida,28-Aug-2018,4
Gopi,27-Aug-2018,4
Ramesh,26-Aug-2018,4
John Saida,25-Aug-2018,4
Gopi,24-Aug-2018,4
Ramesh,23-Aug-2018,4
John Saida,22-Aug-2018,4
Gopi,21-Aug-2018,4
Ramesh,20-Aug-2018,4
John Saida,19-Aug-2018,4
Gopi,18-Aug-2018,4
Ramesh,17-Aug-2018,4
John Saida,16-Aug-2018,4
Gopi,15-Aug-2018,4
Ramesh,14-Aug-2018,5
John Saida,13-Aug-2018,5
Gopi,12-Aug-2018,5
Ramesh,11-Aug-2018,5
John Saida,10-Aug-2018,5
Gopi,9-Aug-2018,5
Ramesh,8-Aug-2018,5
John Saida,7-Aug-2018,5
Gopi,6-Aug-2018,5
Ramesh,5-Aug-2018,5
John Saida,4-Aug-2018,5
Gopi,3-Aug-2018,5
Ramesh,2-Aug-2018,5
John Saida,1-Aug-2018,5
Gopi,31-Jul-2018,5
Ramesh,30-Jul-2018,5
John Saida,29-Jul-2018,5
Gopi,28-Jul-2018,5
Ramesh,27-Jul-2018,5
John Saida,26-Jul-2018,5
Gopi,25-Jul-2018,6
Ramesh,24-Jul-2018,6
John Saida,23-Jul-2018,6
Gopi,22-Jul-2018,6
Ramesh,21-Jul-2018,6
John Saida,20-Jul-2018,6
Gopi,19-Jul-2018,6
Ramesh,18-Jul-2018,6
John Saida,17-Jul-2018,6
Gopi,16-Jul-2018,6
Ramesh,15-Jul-2018,6
John Saida,14-Jul-2018,6
Gopi,13-Jul-2018,6
Ramesh,12-Jul-2018,6
John Saida,11-Jul-2018,6
Gopi,10-Jul-2018,6
Ramesh,9-Jul-2018,6
John Saida,8-Jul-2018,6
Gopi,7-Jul-2018,6
Ramesh,6-Jul-2018,7
John Saida,5-Jul-2018,7
Gopi,4-Jul-2018,7
Ramesh,3-Jul-2018,7
John Saida,2-Jul-2018,7
Gopi,1-Jul-2018,7
Ramesh,30-Jun-2018,7
John Saida,29-Jun-2018,7
Gopi,28-Jun-2018,7
Ramesh,27-Jun-2018,7
John Saida,26-Jun-2018,7
Gopi,25-Jun-2018,7
Ramesh,24-Jun-2018,7
John Saida,23-Jun-2018,7
Gopi,22-Jun-2018,7
Ramesh,21-Jun-2018,7
John Saida,20-Jun-2018,7
Gopi,19-Jun-2018,7
Ramesh,18-Jun-2018,7
John Saida,17-Jun-2018,7
Gopi,16-Jun-2018,8
Ramesh,15-Jun-2018,8
John Saida,14-Jun-2018,8
Gopi,13-Jun-2018,8
Ramesh,12-Jun-2018,8
John Saida,11-Jun-2018,8
Gopi,10-Jun-2018,8
Ramesh,9-Jun-2018,8
John Saida,8-Jun-2018,8
Gopi,7-Jun-2018,8
Ramesh,6-Jun-2018,8
John Saida,5-Jun-2018,8
Gopi,4-Jun-2018,8
Ramesh,3-Jun-2018,8
John Saida,2-Jun-2018,8
];
here it is, PFA
Hi,
Please try like this.
Data:
LOAD date(date#(SalesDate,'MM/DD/YYYY')) as SalesDate,Sales INLINE [
SalesDate,Sales
10/3/2018,1
10/2/2018,2
10/1/2018,3
9/30/2018,1
9/29/2018,2
9/28/2018,3
9/27/2018,1
9/26/2018,2
9/25/2018,3
9/24/2018,1
9/23/2018,2
9/22/2018,3
9/21/2018,2
9/20/2018,2
9/19/2018,2
9/18/2018,3
9/17/2018,3
9/16/2018,3
9/15/2018,3
9/14/2018,3
9/13/2018,3
9/12/2018,3
9/11/2018,3
9/10/2018,3
9/9/2018,3
9/8/2018,3
9/7/2018,3
9/6/2018,3
9/5/2018,3
9/4/2018,3
9/3/2018,3
9/2/2018,3
9/1/2018,3
8/31/2018,4
8/30/2018,4
8/29/2018,4
8/28/2018,4
8/27/2018,4
8/26/2018,4
8/25/2018,4
8/24/2018,4
8/23/2018,4
8/22/2018,4
8/21/2018,4
8/20/2018,4
8/19/2018,4
8/18/2018,4
8/17/2018,4
8/16/2018,4
8/15/2018,4
8/14/2018,4
8/13/2018,5
8/12/2018,5
8/11/2018,5
8/10/2018,5
8/9/2018,5
8/8/2018,5
8/7/2018,5
8/6/2018,5
8/5/2018,5
8/4/2018,5
8/3/2018,5
8/2/2018,5
8/1/2018,5
7/31/2018,5
7/30/2018,5
7/29/2018,5
7/28/2018,5
7/27/2018,5
7/26/2018,6
7/25/2018,6
7/24/2018,6
7/23/2018,6
7/22/2018,6
7/21/2018,6
7/20/2018,6
7/19/2018,6
7/18/2018,6
7/17/2018,6
7/16/2018,6
7/15/2018,6
7/14/2018,6
7/13/2018,6
7/12/2018,6
7/11/2018,6
7/10/2018,6
7/9/2018,7
7/8/2018,7
7/7/2018,7
7/6/2018,7
7/5/2018,7
7/4/2018,7
7/3/2018,7
7/2/2018,7
7/1/2018,7
6/30/2018,7
6/29/2018,7
6/28/2018,7
6/27/2018,7
];
Expression:
Sum({<SalesDate={">$(=Date(Max(SalesDate)-90)) <=$(=Date(Max(SalesDate)))"}>}Sales)
Hi Try like this.
Your problem here is conversion of the date. Date format is DD-MMM-YYYY. So that you should should use date# function as shown in red color.
Data:
LOAD date(date#(Date,'DD-MMM-YYYY')) as Date,Name,Sales INLINE [
Name,Date,Sales
Gopi,2-Oct-2018,1
Ramesh,1-Oct-2018,2
John Saida,30-Sep-2018,3
Gopi,29-Sep-2018,1
Ramesh,28-Sep-2018,2
John Saida,27-Sep-2018,3
Gopi,26-Sep-2018,4
Ramesh,25-Sep-2018,5
John Saida,24-Sep-2018,1
Gopi,23-Sep-2018,2
Ramesh,22-Sep-2018,3
John Saida,21-Sep-2018,1
Gopi,20-Sep-2018,2
Ramesh,19-Sep-2018,3
John Saida,18-Sep-2018,4
Gopi,17-Sep-2018,5
Ramesh,16-Sep-2018,1
John Saida,15-Sep-2018,2
Gopi,14-Sep-2018,3
Ramesh,13-Sep-2018,1
John Saida,12-Sep-2018,2
Gopi,11-Sep-2018,3
Ramesh,10-Sep-2018,4
John Saida,9-Sep-2018,5
Gopi,8-Sep-2018,3
Ramesh,7-Sep-2018,3
John Saida,6-Sep-2018,3
Gopi,5-Sep-2018,3
Ramesh,4-Sep-2018,3
John Saida,3-Sep-2018,4
Gopi,2-Sep-2018,4
Ramesh,1-Sep-2018,4
John Saida,31-Aug-2018,4
Gopi,30-Aug-2018,4
Ramesh,29-Aug-2018,4
John Saida,28-Aug-2018,4
Gopi,27-Aug-2018,4
Ramesh,26-Aug-2018,4
John Saida,25-Aug-2018,4
Gopi,24-Aug-2018,4
Ramesh,23-Aug-2018,4
John Saida,22-Aug-2018,4
Gopi,21-Aug-2018,4
Ramesh,20-Aug-2018,4
John Saida,19-Aug-2018,4
Gopi,18-Aug-2018,4
Ramesh,17-Aug-2018,4
John Saida,16-Aug-2018,4
Gopi,15-Aug-2018,4
Ramesh,14-Aug-2018,5
John Saida,13-Aug-2018,5
Gopi,12-Aug-2018,5
Ramesh,11-Aug-2018,5
John Saida,10-Aug-2018,5
Gopi,9-Aug-2018,5
Ramesh,8-Aug-2018,5
John Saida,7-Aug-2018,5
Gopi,6-Aug-2018,5
Ramesh,5-Aug-2018,5
John Saida,4-Aug-2018,5
Gopi,3-Aug-2018,5
Ramesh,2-Aug-2018,5
John Saida,1-Aug-2018,5
Gopi,31-Jul-2018,5
Ramesh,30-Jul-2018,5
John Saida,29-Jul-2018,5
Gopi,28-Jul-2018,5
Ramesh,27-Jul-2018,5
John Saida,26-Jul-2018,5
Gopi,25-Jul-2018,6
Ramesh,24-Jul-2018,6
John Saida,23-Jul-2018,6
Gopi,22-Jul-2018,6
Ramesh,21-Jul-2018,6
John Saida,20-Jul-2018,6
Gopi,19-Jul-2018,6
Ramesh,18-Jul-2018,6
John Saida,17-Jul-2018,6
Gopi,16-Jul-2018,6
Ramesh,15-Jul-2018,6
John Saida,14-Jul-2018,6
Gopi,13-Jul-2018,6
Ramesh,12-Jul-2018,6
John Saida,11-Jul-2018,6
Gopi,10-Jul-2018,6
Ramesh,9-Jul-2018,6
John Saida,8-Jul-2018,6
Gopi,7-Jul-2018,6
Ramesh,6-Jul-2018,7
John Saida,5-Jul-2018,7
Gopi,4-Jul-2018,7
Ramesh,3-Jul-2018,7
John Saida,2-Jul-2018,7
Gopi,1-Jul-2018,7
Ramesh,30-Jun-2018,7
John Saida,29-Jun-2018,7
Gopi,28-Jun-2018,7
Ramesh,27-Jun-2018,7
John Saida,26-Jun-2018,7
Gopi,25-Jun-2018,7
Ramesh,24-Jun-2018,7
John Saida,23-Jun-2018,7
Gopi,22-Jun-2018,7
Ramesh,21-Jun-2018,7
John Saida,20-Jun-2018,7
Gopi,19-Jun-2018,7
Ramesh,18-Jun-2018,7
John Saida,17-Jun-2018,7
Gopi,16-Jun-2018,8
Ramesh,15-Jun-2018,8
John Saida,14-Jun-2018,8
Gopi,13-Jun-2018,8
Ramesh,12-Jun-2018,8
John Saida,11-Jun-2018,8
Gopi,10-Jun-2018,8
Ramesh,9-Jun-2018,8
John Saida,8-Jun-2018,8
Gopi,7-Jun-2018,8
Ramesh,6-Jun-2018,8
John Saida,5-Jun-2018,8
Gopi,4-Jun-2018,8
Ramesh,3-Jun-2018,8
John Saida,2-Jun-2018,8
];
Expression:
Sum({<Date={">$(=Date(Max(Date)-90)) <=$(=Date(Max(Date)))"}>}Sales)