topic divide month amount by days in month in QlikView

divide month amount by days in month

Thu, 08 Apr 2010 12:59:45 GMT

Hi All,

I have amounts linked to the last day of each month, that I need to convert to a daily amount i.e. amount / days in month.

I've created a calendar so have all the days for each month and have the amount currently showing on the last day of each month, but how in an expression can I say the amount for last day divided by number of days in that month?

Many thanks,
Phil

divide month amount by days in month

Thu, 08 Apr 2010 13:12:39 GMT

hi,

you can use

fieldvaluecount('Days') to get the no of days in 'Days' field for a particular month

thanks

divide month amount by days in month

Thu, 08 Apr 2010 14:16:43 GMT

Hi Tauqueer,

Many thanks for the reply.

The problem isn't so much to do with getting the number of days, my data looks similar to this:

Date Amount

1/1/2010 0
2/1/2010 0
3/1/2010 0
[...]
31/1/2010 500

I want to get the 500 and divide by number of days in the month, so would need to show 500/31 for each day of Jan in this example.

Many thanks,
Phil

divide month amount by days in month

Thu, 08 Apr 2010 14:23:21 GMT

This is my expression for dividing the total by days in the month:


=
sum(if(AimMths>0 And AimMths<4,PRIRENT+PRIDEPN+NON_INS_INC/(monthend(DUEDATE)-monthstart(DUEDATE)),0))

Though I get the correct number it only shows this value on the last day of the month, but I need to show this amount for each day of the month.

Thanks

divide month amount by days in month

Thu, 08 Apr 2010 14:34:40 GMT

hi,

what you can do is that write

load day(Date) as days

in your script

and then instead of monthend(DUEDATE)-monthstart(DUEDATE)

use fieldvaluecount( ' days ') in your expression

thanks

divide month amount by days in month

Thu, 08 Apr 2010 14:58:31 GMT

Hi Tauqueer,

Sorry I'm fairly new to Qlik and I'm not sure how that displays the amount for each day? I.E. not the day number but the total amount for the month (which is currently only shown on the last day) divided by the number of days for each month (which I have now... calculated in the LOAD for the calendar table)

I'm trying to use the aggr function, to get the total for the month then divide that by the number of days... without much luck 😞

Thanks,
Phil

divide month amount by days in month

Thu, 08 Apr 2010 15:34:26 GMT

hi,

can you attach your application here so that i can get a clear picture of what exactly you need .

thanks

divide month amount by days in month

Thu, 08 Apr 2010 16:09:51 GMT

Hi Tauqueer,

I've attached a sample to show the result I'm trying to achieve.

Many thanks for your help with this.

Phil

divide month amount by days in month

Tue, 11 May 2010 07:45:03 GMT

Phil,

Did you find a solution for your problem?

I've got a same thing going on.

Greetz,

Halmar

divide month amount by days in month

Tue, 11 May 2010 09:30:56 GMT

Hi Halmar,

I kind of got there, though didn't go down this route in the end.

I've attached a sample qvw, if you look at the load script I bring in the daily amounts which are 0 apart from the last day of the month, then I create a couple of fields in the load and use these in the qvw expressions.

Hopefully it's pretty easy to follow what I've done, anyway have a look and hope the attached helps.

Kind Regards,
Phil

divide month amount by days in month

Anonymous — Tue, 11 May 2010 12:13:04 GMT

Hi Phil,

I Change little bit your code. Hope this give you the result you want.

Normal 0 21 false false false FI X-NONE X-NONE MicrosoftInternetExplorer4


data:

LOAD * INLINE [
Day, Amount
01/01/2010,0
02/01/2010,0
……
];

EndDays:
LOAD
 Date(Monthend(Day),'DD/MM/YYYY') as MonthEnd,
 Amount as EndAmount
Resident data where Amount>0 and not Isnull(Amount);

AllDays:
LOAD
 Date(Monthend(Day),'DD/MM/YYYY') as MonthEnd,
 Month(Day) & Year(Day) As MthYr,
 Date(Day,'DD/MM/YYYY') as Day
RESIDENT data;


Daily_Amount:
JOIN
LOAD MonthEnd,
 Day(MonthEnd) as NoDays,
 // EndAmount ,
 Num(EndAmount/Day(MonthEnd)) as [Daily Amount]
RESIDENT EndDays;

DROP TABLE data;

Anni

divide month amount by days in month

Tue, 11 May 2010 14:34:47 GMT

Hi Anni,

Many thanks for that, calculating the monthly amount in the load is a much better solution for me.

Kind Regards,
Phil

Re: divide month amount by days in month

Thu, 12 Jul 2012 12:25:55 GMT

I saw this and did try to do exactly the same but found these solutions a bit to advanced.

I just created a variable:

LET MaxDaysInMonth =Day(monthend (Today()));

And this variable will always show the number of days in the month you are in right now.

Re: divide month amount by days in month

v_iyyappan — Thu, 12 Jul 2012 14:28:41 GMT

Hi,

Use the script like this

MasterCalendar :

Num(OrderDate) AS OrderDay,

In FirstChart

Propertities ---> Dimension---> Secor, OrderDate,

Enable the option of Suppress when value is null you will get the remove the 'Zero' value from chart.

Expression like this

=Sum(Amount) / Count(Distinct OrderDay)

Regards,

Iyyappan