Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

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.

PrashantSangle

Hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

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)

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sasiparupudi1
Master III
Master III

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

Anonymous
Not applicable
Author

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.

PrashantSangle

Hi,

then just use

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Anonymous
Not applicable
Author

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

It's not working...