Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
can you help me, i want to sum the sales within 30 day each day. how do i make this in qlikview ?
Try this in the script:
Table:
LOAD Frag,
Date,
Sales
FROM
CH126_20150904_165750.xls
(biff, embedded labels, table is Sheet1$);
NewTable:
LOAD *,
RangeSum(Sales, Peek('Sales'), Peek('Sales', -2), Peek('Sales', -3), Peek('Sales', -4), Peek('Sales', -5),
Peek('Sales', -6), Peek('Sales', -7), Peek('Sales', -8), Peek('Sales', -9), Peek('Sales', -10),
Peek('Sales', -11), Peek('Sales', -12), Peek('Sales', -13), Peek('Sales', -14), Peek('Sales', -15),
Peek('Sales', -16), Peek('Sales', -17), Peek('Sales', -18), Peek('Sales', -19), Peek('Sales', -20),
Peek('Sales', -21), Peek('Sales', -22), Peek('Sales', -23), Peek('Sales', -24), Peek('Sales', -25),
Peek('Sales', -26), Peek('Sales', -27), Peek('Sales', -28), Peek('Sales', -29)) as [30DaySales]
Resident Table
Order By Frag desc;
DROP Table Table;
Output in a table box object:
Can you share your excel file so that we can load it and test your requirement before giving you a solution?
Hi,
Try like this
Straight Table:
Dimension: Frag, Date
Expression:
1. Sum(Sales)
2. Rangesum(Below(Sum(Sales), 2, 30))
Hope this helps you.
Regards,
Jagan.
here's the excel thankyou
Jagan is right, but the offset he suggests is slightly wrong, use
Rangesum(Below(Sum(Sales), 0, 30))
See attachment for solution.
Try this in the script:
Table:
LOAD Frag,
Date,
Sales
FROM
CH126_20150904_165750.xls
(biff, embedded labels, table is Sheet1$);
NewTable:
LOAD *,
RangeSum(Sales, Peek('Sales'), Peek('Sales', -2), Peek('Sales', -3), Peek('Sales', -4), Peek('Sales', -5),
Peek('Sales', -6), Peek('Sales', -7), Peek('Sales', -8), Peek('Sales', -9), Peek('Sales', -10),
Peek('Sales', -11), Peek('Sales', -12), Peek('Sales', -13), Peek('Sales', -14), Peek('Sales', -15),
Peek('Sales', -16), Peek('Sales', -17), Peek('Sales', -18), Peek('Sales', -19), Peek('Sales', -20),
Peek('Sales', -21), Peek('Sales', -22), Peek('Sales', -23), Peek('Sales', -24), Peek('Sales', -25),
Peek('Sales', -26), Peek('Sales', -27), Peek('Sales', -28), Peek('Sales', -29)) as [30DaySales]
Resident Table
Order By Frag desc;
DROP Table Table;
Output in a table box object:
For front end option, you would need this:
Dimension: Frag, Date
Expression
1) Sales
2)=RangeSum(Below(TOTAL Sales, 0, 30))
Output:
@Hope this will help you