topic Re: Date functions in search filter in expression in QlikView

Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 07:09:59 GMT

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.

Re: Date functions in search filter in expression

PrashantSangle — Wed, 01 Jun 2016 07:26:33 GMT

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

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 07:29:08 GMT

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

PrashantSangle — Wed, 01 Jun 2016 07:33:58 GMT

Hi,

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

Regards

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 07:37:36 GMT

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

PrashantSangle — Wed, 01 Jun 2016 07:49:59 GMT

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

Re: Date functions in search filter in expression

sasiparupudi1 — Wed, 01 Jun 2016 08:09:48 GMT

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

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 08:31:45 GMT

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

PrashantSangle — Wed, 01 Jun 2016 09:01:32 GMT

Hi,

then just use

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

Regards

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 09:17:06 GMT

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

It's not working...

Re: Date functions in search filter in expression

sasiparupudi1 — Wed, 01 Jun 2016 09:26:29 GMT

You can not display previous yearmonth counts in the chart with yearmonth as a dimension.

Please try my script .

Re: Date functions in search filter in expression

qlikview979 — Wed, 01 Jun 2016 10:06:50 GMT

Try this,

Regards,

Mahesh

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 10:21:20 GMT

Thanks.

You show me result by YearMonth field. It will be very simple

I need to show result by YearMonth_Temp.

For example, why this formula is worked:

count({<YearMonth={"201505"}>}DISTINCT DeliveryPoint)

But this (which get also YearMonth 201505 for YearMonth_Temp 201506) 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

Wed, 01 Jun 2016 10:34:44 GMT

try this

Re: Date functions in search filter in expression

sasiparupudi1 — Wed, 01 Jun 2016 10:37:36 GMT

Re: Date functions in search filter in expression

Anonymous — Wed, 01 Jun 2016 10:42:30 GMT

Thanks!

Can you tell me, why it's calculated only with one selected YearMonth_Temp?

It show me correct result, but with your formula i need select each value from YearMonth_Temp and collect data in one please.