Date functions in search filter in expression

[Anonymous]

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.

[sasiparupudi1]

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

Please try my script .

[qlikview979]

Hi

Try this,

Regards,

Mahesh

[Anonymous]

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)

?

[#5]

try this

[sasiparupudi1]

[Anonymous]

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.