divide month amount by days in month

[#1]

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

[#2]

hi,

you can use

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

thanks

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

hi,

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

thanks

[#8]

Hi Tauqueer,

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

Many thanks for your help with this.

Phil

[#9]

Phil,

Did you find a solution for your problem?

I've got a same thing going on.

Greetz,

Halmar

[#10]

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