Month Function

[#1]

Hi all,

This is what I am trying to write in QV.

IF Sale Date MONTH = LAST MONTH

Return 'Value1'

ELSE 'Value2' AS [This Month]

LOAD

[Client],
[Sale  Date],
IF( MONTH([Sale Date]) = MONTH()-1,'Value1,'Value2')AS [This Month],

FROM
[..\..\Data\DataSheel.xlsx]
(ooxml, embedded labels, table is Sheet1);

Please kindly assist.

Thank you.

[albertovarela]

Are you trying to evaluate if the Month on your Sale Date field is equal to the previous month (Based on a specific date (e.g. today's date)?

[its_anandrjs]

Then write some thing like

LOAD

[Client],
[Sale  Date],

IF( MONTH([Sale Date]) = Month( AddMonths( Today() ,-1)),'Value1','Value2') AS [This Month]

FROM
[..\..\Data\DataSheel.xlsx]
(ooxml, embedded labels, table is Sheet1);

Regards

Anand

[maxgro]

if start of month of sale date is start of last month

if(floor(MonthStart([Sale Date])) = floor(addmonths(MonthStart(today()),-1)),  'Value1', 'Value2) as ....

[its_anandrjs]

For more see this small example and i assume here you want previous month to as Value1 and rest Value2 for this see the small example load.

tmp:

LOAD Date#( [Sale Date],'DD/MM/YYYY') as [Sale Date] ,Value;

LOAD * Inline

[

Sale Date,Value

27/10/2014,458

27/10/2014,256

26/09/2014,284

26/10/2014,258

];

NoConcatenate

Final:

LOAD

*,

IF( MONTH([Sale Date]) = Month( AddMonths( Today() ,-1) ),'Value1','Value2')AS [This Month]

Resident tmp;

DROP Table tmp;

And you get

Regards,

Anand

[MarcoWedel]

Hi,

in order to get only last months of the same year and to avoid issues with december/january combinations use MonthName instead of Month. This way you can also subtract one month from the current, as the MonthName function has an additional shift parameter:

tabSales:
LOAD [Client],
     [Sale Date], 
     If(MonthName([Sale Date]) = MonthName(Today(),-1),'Value1','Value2') as [This Month]
INLINE [
    Client, Sale Date
    1, 09/10/2014
    1, 09/28/2014
    1, 10/27/2014
    1, 11/15/2014
    2, 07/15/2014
    2, 08/15/2014
    2, 09/15/2014
    2, 10/15/2014
    2, 11/15/2014
    3, 07/15/2013
    3, 08/15/2013
    3, 09/15/2013
    3, 10/15/2013
    3, 11/15/2013
];

hope this helps

regards

Marco

[#7 · Accepted solution]

Hi all,

Thank you very much for all your contribution - please kindly excuse the delay in replying.

I have tried both Anand and Marco's suggestions below and they work well for me. I might end up going with Macro's suggestion simply because it accommodates January/December issues.

Anand: IF( MONTH([Sale Date]) = Month( AddMonths( Today() ,-1)),'Value1','Value2') AS [This Month]

Macro: If(MonthName([Sale Date]) = MonthName(Today(),-1),'Value1','Value2') as [This Month]

Thanks all once again.

I am happy to close this discussion as 'answered'

[MarcoWedel]

Hi,

glad to hear that you could solve your issue.

Please mark one of the threads as correct and/or helpful, that actually answered your question.

thanks

regards

Marco

[its_anandrjs]

Thanks, Do you want to give points to us for this .

Regards

Anand

[#10]

Hi all,

I will also like to say Massimo suggestion below is particularly useful, as it accommodates for January/December issue and also for 'if start of month of sale date is start of last month'

if(floor(MonthStart([Sale Date])) = floor(addmonths(MonthStart(today()),-1)),  'Value1', 'Value2') As [This Month]