Restrict data in IDT to current month only?

[Former Member]

Hi Experts,

How can i restrict my data to Current month only. For me Period field is coming as string like '06/2015'.

Pleas help me on this.

Thanks

[former_member207052 · Accepted solution]

Create a universe filter object directly.

DATE_FORMAT(STR_TO_DATE(Alias_of_MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')

You can make this filter as a mandatory filter and then any report that is built on this universe will be restricted only to the current month data. If you make this optional you can add the filter in the reports as and when required.

[Former Member · Accepted solution]

Hi Narasimhan,

Actually i dont have any record for current month may be because of that i am unable to get the correct result.

Apart from that to get previous 6 months data i am using below SQL expression but it is returning more that 6 months previous data. how can i restrict to 6 month by using current month.

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')<= PERIOD_ADD(DATE_FORMAT(now(),'%Y%m'),-2)

Please help me on this.

Thanks

[former_member207052 · Accepted solution]

Yes, if you have no records for the current month then you will see no results.

If you want to test you can hardcode a date value in the filter (for which you have data in the database) and see if you get results.

You are almost there with the last 6 months formula. Instead of -2 you have to use -6

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')<= PERIOD_ADD(DATE_FORMAT(now(),'%Y%m'),-6)

http://www.w3resource.com/mysql/date-and-time-functions/mysql-period_add-function.php

[Former Member · Accepted solution]

Hi Narasimhan,

I was checking with different numbers like -2,-3,-4 like that. its giving values but i am not getting the values as per my requirement.

Original Table:

Period	kpi Value
01/2015	10
02/2015	20
03/2015	30
04/2015	40
05/2015	50
06/2015	60
07/2015	70

After applying SQL expression i need a output like below.

Period	KPI Value
01/2015	10
02/2015	20
03/2015	30
04/2015	40
05/2015	50
06/2015	60

But using this SQL Expression i am getting result

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')<= PERIOD_ADD(DATE_FORMAT(now(),'%Y%m'),-6)

Period	Kpi Value
01/2015	10

Thanks

[former_member207052 · Accepted solution]

Just realized. The operator should be >= and not <=

Also, make sure that the date conversion is working as expected by running the select statement directly against the db table. I doubt because period add returns full date and you just want month and year. you can give a try with the below filter:

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')>=DATE_FORMAT(PERIOD_ADD(now(),-6),'%Y%m')

[Former Member · Accepted solution]

Hi Narasimhan,

Now i got a one more requirement that. instead of taking current month as now() according to prompt value it has to select previous 6 months values.

Please help me how can i achieve this.

Thanks

[former_member207052 · Accepted solution]

since your column is not in date format, you have to create a new dimension. This is to make sure that the end user can see the calendar control.

Dimension:

KPI_PERIOD_Date: STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y')

In the below definition, replace yourclass with the appropriate universe class.

Filter object definition:

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_KPI_VIEW.period,'%m/%Y'),'%Y%m')>=DATE_FORMAT(PERIOD_ADD(@PROMPT('Enter Date:','D','yourclass\KPI_PERIOD_Date',Mono,Free,persistent),-6),'%Y%m')

[Former Member · Accepted solution]

Hi Narasimhan,

i am trying to implement below SQL Expression and its giving me some error. i think i am giving my class name is wrong.

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_DATA_KPI_VIEW.period,'%m/%Y'),'%Y%m')>=DATE_FORMAT(PERIOD_ADD(@PROMPT('Enter Date:','D','MOL_OBJECTIVE_DATA_KPI_VIEW.period\KPI_PERIOD',Mono,Free,persistent),-6),'%Y%m')

let me know do i need to change any thing.

Thanks

[former_member207052 · Accepted solution]

Yes-correct, you have provided an invalid class name.

For example if my universe is like below, my prompt would be:

DATE_FORMAT(STR_TO_DATE(MOL_OBJECTIVE_DATA_KPI_VIEW.period,'%m/%Y'),'%Y%m')>=DATE_FORMAT(PERIOD_ADD(@PROMPT('Enter Date:','D','Employee\KPI_PERIOD',Mono,Free,persistent),-6),'%Y%m')

[Former Member]

yes you can.

On universe create an object

current_Date =  to_char(sysdate(),'mm/yyyy')

Now on webi report drag the object current_Date and then apply filter on same.

I hope this will help you.

Thanks,

SB