Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
elpmasjj
New Contributor

Daily budget & R30 Sales

Dear Community,

2 questions in one here.

Q1) I'm trying to get a "Daily" budget figure to compare to my sales value.

Sum(Budget) / Day(MonthEnd(DateId)) will give me the figure I'm after, but only for the 1st of each month. I need that value in every day, for the applicable months

Q2) I'd also like a Rolling 30 daily sales average (if possible, in the example data below, utilising the June sales for the July rows that would require it). Accumulate 30 Steps Back won't work because of days with no sales and it won't look back into June.

NOTES:

1) Because of days with no sales (i.e. missing days), the row count does not lend itself to simple Above() expressions.

2) Unchecking the "Suppress zero || missing values" checkbox in Presentation tab is BAD because it shows all possible DateId values, despite selections (i.e. displays every day in my Calendar table)

2) Obviously, there are list box selections elsewhere filtering data, including, but not limited to, a [DateFinYear] field and a [DateMonthName] field which limit the DateId's shown in the straight table chart.

Data looks like this

DateIdSales Sales R30 Budget
01/07/20151740761615612
02/07/20151902130
03/07/20151085310
06/07/2015163390
07/07/2015736420
08/07/2015773970
09/07/2015475860
10/07/2015421570
13/07/2015205920
14/07/2015288100
15/07/20151003660
16/07/2015510630
17/07/20151965070
20/07/20151256200
21/07/2015426330
22/07/2015701500
23/07/2015781220
24/07/2015481710
27/07/2015280740
28/07/2015237680
29/07/2015411310
30/07/2015560700
31/07/2015357390
01/08/201501798036
03/08/20151327130
04/08/2015177460
05/08/2015762950
06/08/20151902130
07/08/2015463280
10/08/2015207780
11/08/2015253680
12/08/2015522150
13/08/20151057670
14/08/2015421980
17/08/2015237290
18/08/2015462840
19/08/2015483060
20/08/2015968690
21/08/20151062740
24/08/2015138900
25/08/2015503610
26/08/2015515930
27/08/2015739500
28/08/2015758840
31/08/20151733260
01/09/2015804871681866
02/09/20151000990
03/09/2015566170
04/09/2015834260
07/09/2015333220
08/09/2015575500
09/09/2015601400
10/09/20151222120
11/09/20151459840
14/09/2015479720
15/09/2015324740
16/09/2015325050
17/09/2015972860
18/09/2015549850
21/09/2015215780
22/09/2015471540
23/09/20151067490
24/09/2015233210
25/09/20151296870
28/09/2015190410
29/09/2015383070
30/09/20151066100
1 Reply

Re: Daily budget & R30 Sales

Accumulate 30 days back can be done very easily with the AsOf Table:The As-Of Table

Table:

LOAD DateId,

    Sales,

    Budget,

    MonthName(DateId) as MonthYear,

    'CY' as Flag

FROM

[https://community.qlik.com/thread/219586]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD DateId as ReportDate,

  Date(DateId -IterNo()+1) as DateId

Resident Table

While IterNo() <= 30;


Capture.PNG

Still working on getting the Budget fixed. Somehow it isn't working for the 31st of the month...

Capture.PNG

Community Browser