topic Monthstart and Monthend in QlikView

Monthstart and Monthend

Wed, 26 Nov 2014 06:25:31 GMT

Hi All,

Can anyone help me in getting the below calculation,

Count({<Date=Monthstart(Date)>}empid)

As per my requirement i should get the sum(Count of Employees in Monthstart of every Month. and month end of every month)

Regards,

Priyanka

Re: Monthstart and Monthend

ashfaq_haseeb — Wed, 26 Nov 2014 06:40:54 GMT

Hi,

This must work

Count({<Date={'$(=(Monthstart(Date)))'}>}empid)

Count({<Date={'$(=(Monthend(Date)))'}>}empid)

Format your date accordingly.

Regards

ASHFAQ

Re: Monthstart and Monthend

PradeepReddy — Wed, 26 Nov 2014 07:47:36 GMT

try like this...

Count({<Date={'$(=(Monthstart(Date)))'}>}empid)

Count({<Date={'$(=(Monthend(Date)))'}>}empid)

Re: Monthstart and Monthend

Wed, 26 Nov 2014 08:29:04 GMT

Hi AShfaq,

I have tried implementing the formula,you mentioned ,but am getting 0 value.

Please find the attached QVW

Re: Monthstart and Monthend

its_anandrjs — Wed, 26 Nov 2014 08:38:51 GMT

Hi,

Try this expressions

=Count({<Date={'$(=(Monthstart(Max(Date))))'}>}empid)

=Count({<Date={'$(=(Monthend(Max(Date))))'}>}empid)

And what is your expected output in this two

Regards

Anand

Re: Monthstart and Monthend

Wed, 26 Nov 2014 08:49:28 GMT

Hi Anand,

Both are not working,If any one of the expressions work,Its fine.

Re: Monthstart and Monthend

Wed, 26 Nov 2014 08:52:52 GMT

Priyana,

I would do this in your script, create new field(s) to evaluate if the date is a month start or month end. Then just use that flag within your set analysis

Load

empid

,Date

,If(Date=MonthStart(Date),1) As MonthStartFlag

,If(Date=MonthEnd(Date),1) As MonthEndFlag

from xxxxx

Then just make your expression

Count({<MonthStartFlag={1}>} empid)

Count({<MonthEndFlag={1}>} empid)

something like that

Hope that helps

Joe

Re: Monthstart and Monthend

jonathandienst — Wed, 26 Nov 2014 08:55:07 GMT

I suspect that the problem is you are trying to use it in a chart or table. The set expression is evaluated once for the chart (outside of the dimensions) and not row by row. If you need to do this row by row, you can use count(if()) or set a month end flag during the load.

count(if()):

count(if(Date = MonthEnd(Date), empid))

flag:

LOAD ...

Date,

If(Date = MonthEnd(Date), 1, 0) As IsMonthEnd,

...

count({<MonthEnd = {1}, empid)

(I include a month end flag in my common master calendar which is in a qvd shared by many models)

HTH

Jonathan

Re: Monthstart and Monthend

its_anandrjs — Wed, 26 Nov 2014 09:06:42 GMT

Hi Priyanka,

Try this way then by adding the fields MonthStart date and MonthEnd Date

LOAD Date,

MonthStart(Date) as MonStartDate,

MonthEnd(Date) as MonEndDate,

empid,

Type

FROM

mockup.xlsx

(ooxml, embedded labels, table is Sheet1);

And then in the front end write expressions in any text object or the straight table

For MonthStart

=Count( if( Date = MonStartDate, empid ))

For MonthEnd

=Count( if( Date = MonEndDate, empid ))

Output you get

Regards

Anand

Re: Re: Monthstart and Monthend

Anonymous — Wed, 26 Nov 2014 09:08:32 GMT

Hi,

Please find the attachment...

this attachment will give u the solution .........

Thanks

Venkata Sreekanth

Re: Monthstart and Monthend

Wed, 26 Nov 2014 09:08:48 GMT

Hi Joe,

That was realy helpful.But Monthend Count is not showing properly.Month end count shows total emp id count.

Re: Monthstart and Monthend

Wed, 26 Nov 2014 09:13:23 GMT

Hi Anadh,

That works for monthstart .But Monthend count shows 0 value.Monthend has values.

Re: Monthstart and Monthend

its_anandrjs — Wed, 26 Nov 2014 09:19:56 GMT

Hi,

In Monthend expression write

=Count( if( trim(Date) = Trim( MonEndDate ) , empid ))

Regards

Anand

Re: Monthstart and Monthend

jyothish8807 — Wed, 26 Nov 2014 09:20:22 GMT

Hi Priyanka,

for month end try like this:

if(Date>=monthstart(Date) and Date<=monthend(Date),1,0) as monthendflag.

hope it helps

Regards

Re: Monthstart and Monthend

Wed, 26 Nov 2014 09:26:51 GMT

That would surely flag every row with a 1 would it not?

Re: Monthstart and Monthend

Wed, 26 Nov 2014 09:29:11 GMT

Hi Priyanka,

no problem glad it helped. Not sure why that would be the case, if the month start works. Want to show your working so far and will see if I can help

cheers

Joe

Re: Monthstart and Monthend

its_anandrjs — Wed, 26 Nov 2014 09:30:12 GMT

Hi Priyanka,

This is your complete script you have the date format issue i thing for this use Trim to remove spaces from field and get the script as

tmp:

LOAD

Date(Date,'M/D/YYYY') AS Date,

MonthStart( Trim(Date)) as MonStartDate,

MonthEnd( trim( Date)) as MonEndDate,

empid,

Type

FROM

mockup.xlsx

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD

Date(Trim(Date)) as Date,

Date( Trim( MonStartDate )) as MonStartDate,

Date(Trim( MonEndDate )) as MonEndDate,

empid,

Type

Resident tmp;

DROP Table tmp;

And then write expressions:-

Monthstart

=Count( if( Date = MonStartDate , empid ))

MonthEnd

=Count( if( (Date) = Trim(MonEndDate) , empid ))

Regards

Anand

Re: Monthstart and Monthend

jyothish8807 — Wed, 26 Nov 2014 09:30:15 GMT

Right, my bad Joe.

Re: Monthstart and Monthend

Wed, 26 Nov 2014 09:31:59 GMT

no worries, just checking it wasn't my brain being funny this early in the morning