Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
Colleagues, i'll try to describe problem which i have in calculating expr with search filter.
1. I have main simple table with YearMonth field and DeliveryPoint
2. Then i make one test independent field YearMonth_Test resident main table
Then in pivot report i try to calculate count DeliveryPoint by YearMonth_Test dimension with such condition:
Get count DeliveryPoint for YearMonth=YearMonth_Test-1.
For this case i set formula which get previous YearMonth from YearMonth_Test:
Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1))&num(Month(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1)),'00' )
But using this formula insight search expr filter doesn't work.
My necessary result:
YearMonth_Test | Count |
201504 | 31 |
201505 | 32 |
201506 | 26 |
201507 | 17 |
If any idea to solve it, will be very glad.
Thanks.
Hi,
Where you want to show it ????
If you want to show it in chart then
use YearMonth as dimension
and Expression would be
count(DISTINCT DeliveryPoint)
Regards
I want to show count by YearMonth_Test dimension.
It is the test model, in which i want to test calculation of similar expression using addmonth function inside filter expr.
Thanks.
Hi,
I dont have QV now so Can you post script???
Regards
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Directory;
Main:
LOAD YearMonth,
DeliveryPoint
FROM
Test.xlsx
(ooxml, embedded labels, table is Sheet1);
Temp:
load
Distinct
YearMonth as YearMonth_Temp
Resident Main
Where YearMonth>=201505;
I need to make changes in expression.
My formula, which doesn't work:
count({<YearMonth={"=Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1))&num(Month(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1)),'00' )"}>}DISTINCT DeliveryPoint)
Hi,
Your Temp and Main table are not associated therefore it will not work.
What are you tying to achieve??
Another thing
What is purpose of below expression??
Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1))
Consider your YearMonth_Temp is 201505
then Date#() convert it into date and
in addmonths() your subtracting month which
then output of this expression AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1) for 201505 is 201504
and output of this Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1)) for 201505 is 2015
which you can achieve without AddMonths()
I hope you got my point. and you need to association between Main and Temp table.
Regards
use a straight table
with yearmonth as the dimension
add 2 expressions
current month count({<MonthIndicator={1}>}DeliveryPoint)
Previous month count({<MonthIndicator={2}>}DeliveryPoint)
use the following script to load data and you may apply your filter for 201505(i did not put it in the script below);
delPo:
load * Inline
[
YearMonth,DeliveryPoint
201501,53
201501,55
201501,57
201501,59
201501,61
201501,63
201501,65
201501,67
201501,69
201501,71
201501,73
201501,75
201501,77
201501,79
201501,81
201501,83
201501,85
201501,87
201501,89
201501,91
201501,93
201501,95
201501,97
201501,99
201501,101
201501,103
201501,105
201501,107
201502,45
201502,47
201502,49
201502,51
201502,53
201502,55
201502,57
201502,59
201502,61
201502,63
201502,65
201502,67
201502,69
201502,71
201502,73
201502,75
201502,77
201502,97
201502,99
201502,101
201502,103
201502,105
201502,107
201503,45
201503,47
201503,49
201503,51
201503,53
201503,55
201503,57
201503,59
201503,61
201503,71
201503,73
201503,75
201503,77
201503,79
201503,81
201503,83
201503,85
201503,87
201503,89
201503,91
201503,93
201503,95
201503,97
201503,99
201503,101
201503,103
201503,105
201503,107
201504,45
201504,47
201504,49
201504,51
201504,53
201504,55
201504,59
201504,61
201504,63
201504,65
201504,67
201504,69
201504,71
201504,73
201504,75
201504,77
201504,79
201504,81
201504,83
201504,85
201504,87
201504,89
201504,91
201504,93
201504,95
201504,97
201504,99
201504,101
201504,103
201504,105
201504,107
201505,34312
201505,47
201505,49
201505,51
201505,53
201505,55
201505,544
201505,59
201505,61
201505,63
201505,65
201505,67
201505,69
201505,71
201505,73
201505,75
201505,77
201505,79
201505,81
201505,83
201505,85
201505,87
201505,89
201505,91
201505,93
201505,95
201505,97
201505,99
201505,101
201505,103
201505,105
201505,107
201506,45
201506,47
201506,49
201506,51
201506,53
201506,55
201506,1
201506,59
201506,61
201506,63
201506,65
201506,67
201506,69
201506,71
201506,6
201506,75
201506,77
201506,3
201506,81
201506,83
201506,5
201506,87
201506,89
201506,91
201506,2
201506,95
201507,63
201507,65
201507,67
201507,69
201507,71
201507,6
201507,75
201507,77
201507,3
201507,81
201507,83
201507,5
201507,87
201507,89
201507,91
201507,2
201507,95
201508,69
201508,71
201508,6
201508,75
201508,77
201508,3
];
transactions:
LOAD
Date#(YearMonth,'YYYYMM') as YearMonth ,
DeliveryPoint,
1 as MonthIndicator
Resident delPo;
concatenate LOAD
Date(Addmonths(Date#(YearMonth,'YYYYMM'),1),'YYYYMM') as YearMonth ,
DeliveryPoint,
2 as MonthIndicator
Resident delPo;
drop Table delPo;
hth
Sasi
My main question:
Consider YearMonth_Temp is 201505 expression Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1))&num(Month(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1)),'00' ) get 201504.
I think, that expression must be calculated by following logic:
count({<YearMonth={"=Year(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1))&num(Month(AddMonths( date#(YearMonth_Temp,'YYYYMM'),-1)),'00' )"}>}DISTINCT DeliveryPoint) <=>
count({<YearMonth={201504}>}DISTINCT DeliveryPoint) = 31
I hope, you understand what i need.
Thanks for response.
Hi,
then just use
date(AddMonths(date#(YearMonth,'YYYYMM'),-1),'YYYYMM')
Regards
=count({<YearMonth={"=date(AddMonths(date#(YearMonth_Temp,'YYYYMM'),-1),'YYYYMM')"}>}DISTINCT DeliveryPoint)
It's not working...