topic Re: Round up date calculation to whole number (for text objects) in QlikView

Round up date calculation to whole number (for text objects)

Tue, 17 Nov 2015 21:36:40 GMT

I created a variable with the below formula: =vTotalWeeks

=(Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7

It calculates correctly, but I need this to always round up to the next highest integer. So, 8.642343 should become 9.

Note: I am using text objects so I can't leverage the Number tab of a chart object.

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Tue, 17 Nov 2015 21:45:31 GMT

Try the Ceil() function

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7)

Re: Round up date calculation to whole number (for text objects)

Tue, 17 Nov 2015 21:52:09 GMT

Thanks Sunny this worked. Just one more follow up if i could.

I added another formula: =vWeeksConsumed / vTotalWeeks

This is made up of the below formula. I need this to return % with 2 decimals. 54.95% for example.

=([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date])

Thanks again for your help

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Tue, 17 Nov 2015 22:00:58 GMT

You want still round it up??

If yes, then try:

=Num(Ceil(([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date]), 0.01), '##.00%')

=Num(Round(([Week End] - [Project Start Date]) / ([Project End Date] - [Project Start Date]), 0.01), '##.00%')

Re: Round up date calculation to whole number (for text objects)

Wed, 18 Nov 2015 13:29:41 GMT

Thanks Sunny. I ended up going with the below....your advice was helpful!!

=num(($(=$(vWeeksConsumed)) / num(vTotalWeeks)),'##.00%')

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Wed, 18 Nov 2015 14:41:19 GMT

Great, as long as I am helpful

Re: Round up date calculation to whole number (for text objects)

Wed, 25 Nov 2015 19:17:34 GMT

Hi Sunny, if you are still available I had one follow up to this direct item.

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')) / 7)

This formula is working most of the times, but I have encountered an issue where I believe I need to amend this.

I have an example where Project Start Date = 10/26/15 and Project End Date = 11/30/15....This works out to 36 days which divided by 7 = 5.14. This formula is returning 5 and for my purposes, I need it return 6 weeks.

Do you have any suggestions to accomodate?

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Wed, 25 Nov 2015 19:28:41 GMT

Ceil usually takes it to the next nearest integer So something like this Ceil(36/7) = 6. Is it not doing that?

Re: Round up date calculation to whole number (for text objects)

Wed, 25 Nov 2015 20:58:41 GMT

Not sure if you can see the snapshot below but the Total Weeks is returning 5 for this project and I was expecting 6 [36 / 7]. This in turn impacts the % Weeks Consumed and Remaining which are referencing this formula (variable).

I have reviewed a ton of info on the community especially around date calculations and just can't figure this one out.

One other question if I could. Below for % Budget Remaining, my output is .802943....I need this to be 80.29%. This is my first time trying the below type formula; I obviously have something just slightly wrong for it not to respect the ##.00%. Again, any advice is appreciated. You've been very helpful!

=1.00 - (num($(=$(vActualRev)) / sum([Original Rev Est]),'##.00%'))

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Wed, 25 Nov 2015 21:59:10 GMT

Can you check what you get when you do a simple Subtraction of the two dates:

Num(Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY')), '#,##0.000000')

I have a feeling that this might give you 35 or less than 35. I think once we know what we are getting, we might need to add a 1 here to make sure we get the right number we are hoping for:

=Ceil((Date#([Project End Date], 'MMM DD, YYYY') - Date#([Project Start Date], 'MMM DD, YYYY') + 1) / 7)

But this is pure speculation right now and will have to be tested out.

Let me know what you get and we can proceed from there.

Best,

Sunny

Re: Round up date calculation to whole number (for text objects)

Mon, 30 Nov 2015 14:00:44 GMT

Hi Sunny, I tried your suggestion and you're right, it did return 35. Since the actual days are really 36, I then tried your next suggestion of adding 1 and this did end up returning my expected results for this one specific project. Thank you. I will monitor this for other projects with various project dates.

One last question if I could. Below formula is yielding .80294#######. I need it to return 80.29%. This is my 1st attempt at appending "1.00" at the beginning, so I know i am doing something just slightly wrong to not get the correct % format.

=1.00 - (num($(=$(vActualRev)) / sum([Original Rev Est]),'##.00%'))

Re: Round up date calculation to whole number (for text objects)

sunny_talwar — Mon, 30 Nov 2015 17:09:57 GMT

To keep the formatting, you need to the Num() function around the complete calculations. Try something like this may be:

=Num(1.00 - ($(=$(vActualRev)) / Sum([Original Rev Est])),'##.00%')

Please check the parenthesis because I might have messed that up.