How to find weekly sum of the quantity

building reports webintelligence
1

Hi Experts,

In my Webi report, I have a date column named “Pdate” which has weekly dates. I have to find sum of the “Qty” object prior to each week.

Example:

Pdate

28-12-15
04-01-16
07-01-16
14-01-16
........

I want to find that sum of [Qty] which was before 28-12-15, 04-01-16, 0701-16, 14-01-16

i.e like this

Pdate                                    Sum([Qty])

28-12-15                                 40
04-01-16                                 44
07-01-16                                 51
14-01-16                                 53
........                               ----------------

where 40 is the sum of the [Qty] for the [Sdates] before 28-12-15, 44 is the sum of the [Qty] for the [Sdates] before 04-01-16, 51 is the sum of the [Qty] for [Sdaes] before 07-01-16 and so on…

I have used this formula

Sum([Qty] where([Sdates] < Pdate

where [Sdates] is the object in the universe and Pdates are the calculated weekly dates from [Sdates] for a specific range.

Appreciate your help!!!

Thank you!!!

Afreen (BOB member since 2016-10-25)

2

If 28-12-15 has 40 and 04-01-16 has 4, then you could use the RunningSum formula for what you need. I would expect that either that formula or Previous would work for you, depending upon the underlying data.

3

Thanks Mark for your reply! But how to get that 40? Because in [Sdate] there are some dates which are less than 28-12-2015. i.e [Sdate] starts with date 24-10-2015, and there are four dates against which the sum becomes equal to “40”.

I can find the sum only for one week but finding it hard to calculate sum on weekly basis.

Please help!

Thanks!

Afreen (BOB member since 2016-10-25)

4

It’s difficult for me to answer without knowing your original data returned by the query.

How is 28-12-15 decided upon? Is that via a prompt or input control or something else?

5

Actually 28-12-2015 has been taken from [Sdate] as a starting date for Pdate and I have put condition on [Sdate] like this

Pdate = RelativeDate(LastDayOfWeek([Sdate]) ;-6 Where([Sdate]) >="28-12-2015")

This gives me Pdate from [Sdate] on weekly basis but starting from “28-12-2015”

Afreen (BOB member since 2016-10-25)

6

I’d be careful hardcoding dates. What’s the reporting requirement?

7

I have attached the sample for the requested report. The first column “Date Range” I have got from [Sdate] by using this formula.

=RelativeDate(LastDayOfWeek([Sdate]) ;-6)  + " - " +  LastDayOfWeek([Sdate])

Now I need to find sum of [Qty] before the starting day of each week. i.e how much sum before the starting date of the week (“28-12-15”), how much sum of [Qty] before the starting data of the next week(“04-01-16”) and so on uptill the previous week of the current date.

So, that’s why I calculated [Pdate]which is starting date of each week.
finding sum.xls (35.0 KB)

Afreen (BOB member since 2016-10-25)

8

Any suggestions Mark ?

Afreen (BOB member since 2016-10-25)

9

Please don’t post-bump - it’s against board rules.

Also note that I’m helping out on a volunteer basis and have my own work to do - unless you can outbid my current day rate. :wink:

I cannot download xls files in the office - I’ll take a look this evening (UK time).

10

OK, so are you able to get all columns in that table?

Which inventory figure do you have?

11

Thanks for the reply and sorry for doing against the Board rules.

The attached report is just the sample report. I am still unable to get any value in the inventory column.(For the time being, I am considering only inventory column. )

Thanks!

Afreen (BOB member since 2016-10-25)

12

Until you know where you get inventory numbers from as either a starting inventory or an ending inventory there’s not much we can do to help. You’ll need to ask your universe designer where you can get inventory numbers from. The two general rules of stock are:

Ending inventory = Starting Inventory + Purchases - Sales

Starting Inventory = Ending Inventory - Purchases + Sales

13

I have rules to find inventory and I have mentioned this before that I can do the same report only for one week with correct results but for every week like in this case, I am getting it hard to do. If I get the sum of [Qty] for the dates before the starting date of the first week i.e. in this case, 28-12-2015 then for the next ones I can get by using RunningSum…I think.

Regards,

Afreen (BOB member since 2016-10-25)

14

Potentially have a second data provider then to bring in your start quantity. Then you could have a running sum as you say. :slight_smile:

15

Can you set up a week number variable for your date variable and do a running sum using the week number (using the week function with your date) as the reset variable?

Maddye :uk: (BOB member since 2009-01-09)

16

I used the second data provider and put a filter for [Sdates] less than “28-12-2015”. This gives me the sum of quantity before the week accurately but when I place that qty in front of the date (which is the report requirement, the value doesn’t show up :frowning: . the cell remains blank.

Any Idea?

Afreen (BOB member since 2016-10-25)

17

Sorry Maddey, I couldn’t get what you suggested. Would you please explain a bit more ?

Many thanks!!

Afreen (BOB member since 2016-10-25)

18

At the moment I’m working from memory so you’ll need to check the syntax (I’m most familiar with Deski and the Webi syntax doesn’t always match):

I’m not sure which date variable you want to use, but create a variable called week number which uses the date variable as the input object - week(date variable).

Then create another variable for your weekly sum using runningsum(quantity,week number).

Create a 2-column table and drop week number in the first column, and the weekly sum in the second.

You may need to fiddle around a bit… (I always tend to do this sort of report by trial and error.)

Hope this makes some sense! (You may want to take a look at dagira.com - there’s a lot of tips there - if I have to fiddle around with calculation contexts and resets I usually end up reading various blog articles there first.)

Maddye :uk: (BOB member since 2009-01-09)