cancel
Showing results for 
Search instead for 
Did you mean: 

Quantity for prior month in webi based on Lead Month number

vaibhav_rathore
Participant
0 Kudos

Hi Experts
I have requirement where Any values that fall in months prior to
the current month are to be aggregated and output in the “Overdue”
column based on lead month.

Considering current month is Feb 24

For example: The sum of quantity must be shown in Overdue column if it falls below current month

vaibhav_rathore_2-1708431009909.png

there are 3 values that falling before February: 52,1644,41.1

vaibhav_rathore_1-1708430952097.png

Please advise how it can be done

Thanks

 

View Entire Topic
nscheaffer
Active Contributor

It is difficult to discern what you are trying to do from your screenshots. For example, it is not apparent how your Overdue object values are derived given the visible data. You could create a sample data set by following this method...

Use Free-Hand SQL to Increase the Likelihood of Getting an Answer

I think I now understand your requirement so I'll give it another try. Here is my sample data as free-hand SQL and an image...

nscheaffer_0-1708565998559.png

I am going to start by transforming the data into a crosstab. You did not mention using a crosstab, but that is the only way I can figure out how to get close to what you have shown.

nscheaffer_2-1708567712339.png

I need a series of variables to come up with a total for a differing number of months per row.

First, create a Var First of Current Month variable...

=ToDate(FormatNumber(Year(CurrentDate()); "####") + "-" + FormatNumber(MonthNumberOfYear(CurrentDate()); "0#") + "-01"; "yyyy-MM-dd")

Next, create a Var Start Date Range variable...

=RelativeDate([Var First of Current Month]; [Lead Months] * -1; MonthPeriod)

Finally, use those two variables to create a third Var Previous X Months Total variable...

=Sum([Count]) Where([First of Month] >= [Var Start Date Range] And [First of Month] < [Var First of Current Month])

I added Var Start Date Range to more easily see what is going on. The same for my yellow highlights which I did after exporting it to Excel

nscheaffer_3-1708568029714.png

Hopefully, this gets you on the right track.

Noel