Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

Date functions in search filter in expression

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_TestCount
20150431
20150532
20150626
20150717

If any idea to solve it, will be very glad.

Thanks.

15 Replies

Re: Date functions in search filter in expression

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

Regards,
Prashant Sangle
andreyfcdk91
New Contributor III

Re: Date functions in search filter in expression

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.

Re: Date functions in search filter in expression

Hi,

I dont have QV now so Can you post script???

Regards

Regards,
Prashant Sangle
andreyfcdk91
New Contributor III

Re: Date functions in search filter in expression

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)

Re: Date functions in search filter in expression

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

Regards,
Prashant Sangle
sasiparupudi1
Honored Contributor III

Re: Date functions in search filter in expression

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

andreyfcdk91
New Contributor III

Re: Date functions in search filter in expression

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.

Re: Date functions in search filter in expression

Hi,

then just use

date(AddMonths(date#(YearMonth,'YYYYMM'),-1),'YYYYMM')

Regards

Regards,
Prashant Sangle
andreyfcdk91
New Contributor III

Re: Date functions in search filter in expression

=count({<YearMonth={"=date(AddMonths(date#(YearMonth_Temp,'YYYYMM'),-1),'YYYYMM')"}>}DISTINCT DeliveryPoint)

It's not working...

Community Browser