topic Re: Month Function in QlikView

Month Function

Mon, 27 Oct 2014 18:13:54 GMT

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.

Re: Month Function

albertovarela — Mon, 27 Oct 2014 18:24:59 GMT

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)?

Re: Month Function

its_anandrjs — Mon, 27 Oct 2014 18:25:59 GMT

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

Re: Month Function

maxgro — Mon, 27 Oct 2014 18:34:39 GMT

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 ....

Re: Month Function

its_anandrjs — Mon, 27 Oct 2014 18:44:20 GMT

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

Re: Month Function

MarcoWedel — Mon, 27 Oct 2014 22:35:27 GMT

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

Re: Month Function

Tue, 28 Oct 2014 09:55:06 GMT

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'

Re: Month Function

MarcoWedel — Tue, 28 Oct 2014 10:20:40 GMT

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

Re: Month Function

its_anandrjs — Tue, 28 Oct 2014 10:37:39 GMT

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

Regards

Anand

Re: Month Function

Tue, 28 Oct 2014 10:41:27 GMT

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]

Re: Month Function

Tue, 28 Oct 2014 10:49:33 GMT

Hi Marco,

Massimo, Anand and your suggestions have been very helpful. I have marked Anand and yours. I am happy to mark Massimo but it is seem the option is not there on Massimo's windows.

Regards

Re: Month Function

Tue, 28 Oct 2014 10:50:17 GMT

Please how do I give points? I am happy to..