topic Count Function in QlikView

Count Function

Anonymous — Sun, 18 May 2014 12:23:28 GMT

Hi all,

I have a requirement where we need to calculate a value like (sales/ No of months lapsed).

Ex: if we will select a year 2013 and month='July' then it should calculate the value like sales/6.

So any body can suggest how to calculate the no of months lapsed .

Re: Count Function

anbu1984 — Sun, 18 May 2014 12:25:48 GMT

Sales/Num(GetFieldSelection(MonthField))

Re: Count Function

Anonymous — Sun, 18 May 2014 12:39:50 GMT

its not working...we need the number of that month selection.

By using num function its not coming.

Re: Count Function

MK_QSL — Sun, 18 May 2014 12:50:37 GMT

SUM(Sales)/NUM(Max(Month)-1)

Re: Count Function

Anonymous — Sun, 18 May 2014 13:29:30 GMT

not working

Re: Count Function

its_anandrjs — Sun, 18 May 2014 13:38:04 GMT

Try with this

Sum(Sales) / Max(Month)-1

Or if possible share the sample file.

Re: Count Function

PrashantSangle — Sun, 18 May 2014 14:33:24 GMT

Hi,

Try This,

SUM(Sales)/NUM(Max(Month))-1

Just wanted to know how you calculating Month?

Regards,

Re: Count Function

antoniotiman — Sun, 18 May 2014 14:40:20 GMT

Hi,

Your Field Month is ='July' or =7 ?

Re: Count Function

Sun, 18 May 2014 14:53:31 GMT

You can try this :=sum(Sales)/(num(Month)-1).

=num(Month) will give you the int related to the month selected.

Re: Count Function

Anonymous — Sun, 18 May 2014 15:33:27 GMT

Field month is July,but i need the number 7.

Re: Count Function

Anonymous — Sun, 18 May 2014 15:37:20 GMT

We have normal month selection like January,Feburary,...December..
But we need the number format of months for that expression to calculate months lapsed.

Re: Count Function

antoniotiman — Sun, 18 May 2014 15:46:41 GMT

If you have Field Date (format mm/dd/yyyy) You can try :

Sum(Sales)/(Num(Left(Max(Date),2)) -1)

but this don't work if You choose 'Jan'

Re: Count Function

its_anandrjs — Sun, 18 May 2014 17:10:58 GMT

For any Month fields which is contains June,July so far but if you try like

=Max(Month) it will gives you 7 and you can also try

=Num(Max(Month)) will will also gives you 7

For your requirement you can try

Sum(Sales) / Max(Month)

Sum(Sales) / Num(Max(Month))

as other members also suggest the same.

Re: Count Function

maxgro — Sun, 18 May 2014 18:04:21 GMT

maybe

=max(match(Month,'January','February','March','April','May','June','July','August','September','October','November','December'))

Re: Count Function

MK_QSL — Mon, 19 May 2014 05:02:32 GMT

NUM(Date(Date#(Your Month field, 'MMM'),'MMM'))

Re: Count Function

Anonymous — Mon, 19 May 2014 05:59:15 GMT

By using month(date#(MONTH FILELD ,'MMM')) it worked.

Thanks all for ur help.

Re: Count Function

Mon, 19 May 2014 06:09:50 GMT

While loading the Month field, add one more field of associated month numbers like field name 'MonthNum'.

Then use the below expression:

sum(Sales)/(max(MonthNum)-1)

Hope this will help you...!

Re: Count Function

MK_QSL — Mon, 19 May 2014 18:58:36 GMT

Please close the thread by selecting appropriate answers so that others having similar problems can use.