topic Re: Sum in QlikView

Sum

soha1902 — Thu, 05 Nov 2015 08:02:10 GMT

Hi All,

I have one requirement. In my dashboard I have 1 Dimension Date and 1 measure Values.

Now My Requirement is I want to sum last 30 days data for every date. For example if It is 1 Nov 2015 then it will show sum of 1 Oct 2015 to 31 Oct 2015. if 2nov 2015 then from 2 oct 2015 till 1 nov2015.

Sample File is attached.

Re: Sum

Chanty4u — Thu, 05 Nov 2015 08:10:54 GMT

use accumulation sum for that.

RangeSum(Above(TOTAL Sum(Measure), 0, RowNo(TOTAL)))/sum(total Measure)

Thanks

suresh

Re: Sum

morganaaron — Thu, 05 Nov 2015 08:18:33 GMT

Consider using an As Of table:

The As-Of Table

Re: Sum

sasikanth — Thu, 05 Nov 2015 08:33:21 GMT

HI,

try some thing like this

Load Date,

Date(Date#(Date,'YYYYMMDD'),'MM/DD/YYYY') as New_Date,

Values from Table;

Expression:

=Sum({<Date={">=$(=Date(AddMonths(max(New_Date),-1),'MM/DD/YYYY'))<=$(= Date(max(New_Date)-1),'MM/DD/YYYY')"}>}Value)

Re: Sum

jonathandienst — Thu, 05 Nov 2015 08:37:39 GMT

You can't easily do that with your current data model. Firstly, your date field is not in a proper date format, so you cannot use normal date functions and date arithmetic to calculate "date - 30 days". Secondly, you will need a date island to use dates as both a dimension and as a row-specific filter.

1. Date functions - load the date using

Month:

LOAD Date#(Date, 'YYYYMMDD') As Date,

Value

FROM

C:\Users\Desktop\Sheet.xls

(biff, embedded labels, table is Sheet1$);

2. Create a date island. This is a table containing duplicate values of the dates, but not connected in any way with the rest of the data mode.

DateIsland:

LOAD Distinct Date As IsDate

Resident Month;

Now you can use IsDate as the table dimension, and use the expression:

Sum(If(Date > (IsDate - 30) And Date <= IsDate, Value)

Re: Sum

Fri, 06 Nov 2015 09:28:02 GMT

Hi.

Maybe the good way all calculation do in script like below:

Data_01:

LOAD * Inline [

DATE,             VALUE

01.01.2015,        1

02.01.2015,        10

03.01.2015,        55

04.01.2015,        33

05.01.2015,        5

06.01.2015,        99

07.01.2015,        100

08.01.2015,        110

09.01.2015,        200

10.01.2015,        201

11.01.2015,        50

12.01.2015,        60

13.01.2015,        70

14.01.2015,        900

15.01.2015,        1

16.01.2015,        2

17.01.2015,        888

18.01.2015,        55

19.01.2015,        301

20.01.2015,        232

21.01.2015,        304

22.01.2015,        304

23.01.2015,        305

24.01.2015,        306

25.01.2015,        307

26.01.2015,        404

27.01.2015,        504

28.01.2015,        604

29.01.2015,        304

30.01.2015,        304

31.01.2015,        304

01.02.2015,        12

02.02.2015,        2

03.02.2015,        52

04.02.2015,        3

05.02.2015,        5

06.02.2015,        9

07.02.2015,        10

08.02.2015,        11

09.02.2015,        20

10.02.2015,        12

11.02.2015,        2

12.02.2015,        6

13.02.2015,        7

14.02.2015,        92

15.02.2015,        1

16.02.2015,        2

17.02.2015,        82

18.02.2015,        5

19.02.2015,        32

20.02.2015,        22

21.02.2015,        3

22.02.2015,        3

23.02.2015,        3

24.02.2015,        30

25.02.2015,        32

26.02.2015,        4

27.02.2015,        5

28.02.2015,        65

];

STORE * from Data_01 into Data_01.qvd (qvd);

DROP Table Data_01;

Calendar:

LOAD Concat( Distinct num(DATE), ', ') as DATE_01

From Data_01.qvd (qvd);

LET v_Date = FieldValue('DATE_01', 1);

DROP Table Calendar;

FOR Each i in $(v_Date)

    Data:

    LOAD $(i) as DATE,

         sum(VALUE)

    FROM Data_01.qvd (qvd)

    Where DATE <= $(i) and DATE >= $(i) - 30

    Group By $(i);

NEXT i

Left Join (Data)

LOAD DATE,

     VALUE

FROM Data_01.qvd (qvd);