How to write set analysis to sum of Sales for 90 days?

[jonesbrown]

Hi All

I am new to Qlikview

How to write set analysis to sum of Sales for 90 days?

Plz help.

[tresesco]

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.

[YoussefBelloum]

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

[jonesbrown]

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

];

[YoussefBelloum]

here it is, PFA

[qlikviewwizard · Accepted solution]

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)

[qlikviewwizard]

[qlikviewwizard]

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)