Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a table where i have a sales of Qty. I want to display as like if i select January it should display January total Qty like wise if i select August it should display upto August total Qty.
Ex: I have the following data
Date,Qty
01-01-2015,1
02-02-2015,2
03-03-2015,2
04-04-2015,
05-05-2015,
15-06-2015,5
02-12-2014,6
03-11-2014,7
04-10-2014,8
05-09-2014,9
01-01-2014,1
02-02-2014,2
03-03-2014,2
04-04-2014,
05-05-2014,
15-06-2014,5
02-12-2015,6
03-11-2015,7
04-10-2015,8
05-09-2015,9
Script:
Table:
LOAD Qty,
Date#(Date, 'DD-MM-YYYY') as Date,
Month(Date#(Date, 'DD-MM-YYYY')) as Month,
Year(Date#(Date, 'DD-MM-YYYY')) as Year,
MonthName(Date#(Date, 'DD-MM-YYYY')) as MonthYear;
LOAD * Inline [
Date,Qty
01-01-2015,1
02-02-2015,2
03-03-2015,2
04-04-2015,0
05-05-2015,0
15-06-2015,5
02-12-2014,6
03-11-2014,7
04-10-2014,8
05-09-2014,9
01-01-2014,1
02-02-2014,2
03-03-2014,2
04-04-2014,0
05-05-2014,0
15-06-2014,5
02-12-2015,6
03-11-2015,7
04-10-2015,8
05-09-2015,9
];
Dimension: MonthYear
This expression:
=If(Sum(Qty) > 0, RangeSum(Above(Sum({<MonthYear = , Year = {"=$(=Max(Year))"}>}Qty), 0, RowNo())))
Output:
Something like this:
PFA a qvw document for reference.
Best,
Sunny
Hi sunindia
Could you please provide expression.
Expression was very simple: =Sum(Qty)
sunindia,
I want to display as if i select Jan 2015 it should show Qty as 1 likewise if i select June 2015 it should display upto June total Qty as 10.
June would be Qty = 5, right:
Date,Qty
01-01-2015,1
02-02-2015,2
03-03-2015,2
04-04-2015,
05-05-2015,
15-06-2015,5
02-12-2014,6
03-11-2014,7
04-10-2014,8
05-09-2014,9
01-01-2014,1
02-02-2014,2
03-03-2014,2
04-04-2014,
05-05-2014,
15-06-2014,5
02-12-2015,6
03-11-2015,7
04-10-2015,8
05-09-2015,9
Script:
Table:
LOAD Qty,
Date#(Date, 'DD-MM-YYYY') as Date,
Month(Date#(Date, 'DD-MM-YYYY')) as Month,
MonthName(Date#(Date, 'DD-MM-YYYY')) as MonthYear;
LOAD * Inline [
Date,Qty
01-01-2015,1
02-02-2015,2
03-03-2015,2
04-04-2015,
05-05-2015,
15-06-2015,5
02-12-2014,6
03-11-2014,7
04-10-2014,8
05-09-2014,9
01-01-2014,1
02-02-2014,2
03-03-2014,2
04-04-2014,
05-05-2014,
15-06-2014,5
02-12-2015,6
03-11-2015,7
04-10-2015,8
05-09-2015,9
];
Dimension: MonthYear
Expression: Sum(Qty)
Output:
Nope, for June it should display 10.....I mean it should add Jan, feb, march.....upto June
Oh okay, got it. Let me work on that
Script:
Table:
LOAD Qty,
Date#(Date, 'DD-MM-YYYY') as Date,
Month(Date#(Date, 'DD-MM-YYYY')) as Month,
Year(Date#(Date, 'DD-MM-YYYY')) as Year,
MonthName(Date#(Date, 'DD-MM-YYYY')) as MonthYear;
LOAD * Inline [
Date,Qty
01-01-2015,1
02-02-2015,2
03-03-2015,2
04-04-2015,0
05-05-2015,0
15-06-2015,5
02-12-2014,6
03-11-2014,7
04-10-2014,8
05-09-2014,9
01-01-2014,1
02-02-2014,2
03-03-2014,2
04-04-2014,0
05-05-2014,0
15-06-2014,5
02-12-2015,6
03-11-2015,7
04-10-2015,8
05-09-2015,9
];
Dimension: MonthYear
This expression:
=If(Sum(Qty) > 0, RangeSum(Above(Sum({<MonthYear = , Year = {"=$(=Max(Year))"}>}Qty), 0, RowNo())))
Output:
Thank You Sunny:)