Rolling Last 3 months data with no impact of year change

[mramitbhandari17]

Hi ,

I would like to know that in where clause could you we set a criteria for rolling last 3 months data.

Moreover, if year change, it will still give last 3 months data. For example, Oct'23, Nov'23, Dec'23 has requires in the report however report is going to publishJan'24 with last 3 months data.

And next Feb'24 month, data should contains Nov'23, Dec'23 and Jan'24 automatically.

Is it possible in WHERE Clause only. 

Pls suggest

[anat]

some thing like below 

set vNumMonthsToLoad = 3;

Let vMonthsToLoad = date(addmonths(Today(), -$(vNumMonthsToLoad), 'M/D/YYYY');  

SQL Select

  <fields>

From <db>

Where PODueDate >= '$(vMonthsToLoad )';

[mramitbhandari17]

No working at all. Any other around way to in WHERE clause. 

[PrashantSangle]

@mramitbhandari17 , Logic which @anat  provided must work, what is not working at all for you. what error you are getting? can you provide us with screenshot, data or qvf to help you with better solution else we have guess the solution for you and it will not work at all.

[mramitbhandari17]

Below is the issue :while using code

The following error occurred:
Connector reply error: SQL##f - SqlState: HY000, ErrorCode: 400, ErrorMsg: [Elastic][EsODBC 8.6.0(v8.4.0-4-g3aaf735,u,r) Driver]search_phase_execution_exception: Partial shards failure

[PrashantSangle]

it is connector's error. Can you share your script? also, from which DB you are fetching  data??

[mramitbhandari17]

Elastic search ODBC connector has been using for the script. Could you let me know that 

set vNumMonthsToLoad = 3;

Let vMonthsToLoad = date(addmonths(Today(), -$(vNumMonthsToLoad), 'M/D/YYYY'); 

LOAD <field>;

SQL Select

  <fields>

From <db>

Where PODueDate >= '$(vMonthsToLoad )';

Above query is like that.

[mramitbhandari17]

Can you suggest above query is right way to use that variable in the Query editor.

[anat]

yes ,it should work...

before that check PODueDate field for like 'M/D/YYYY'

[mramitbhandari17]

PODueDate='' is showing blank while set up variable as suggested.