String Fuctions in Scripting And Script Expressions

[ngrunoz]

I want to analyze data of 1000s of our suppliers. We Ask them of Hundreds of questions in weekly Cycles. Questions on productivity output and projections we repeat them each and every week. but the Problem is that some of the questions are auto generated and create a  Check digit to differentiate them from the previous one and the other ones do not.

I have filtered out the productive questions Using the Where Clause .

Requirements:

1. I would Like  to remove The Check digit from the questions to enable weekly comparison not which string function to use.
2. I would Like to present the data in a table by supplier and by day of visitation like on the table Below
3. I would to calculate the weekly data on a cumulative basis.

Date Responded	Week	Miner Details	Projected selling date	Light Distillates ( Ltrs)	Middle Distillates ( Ltrs)	Heavy Distillates ( Ltrs)	Projected Sales Volume	Sales To-date	Balance	Projected Output  (Ltr)
19/10/2016	Week 1	AZ111112	30/10/2016 2:36:17 PM	400	80	150	250	250	119,750	120,000
20/10/2016	Week 1	AZ111111	23/10/2016  6:08:00 PM	500	100	200	300	300	699,700	700,000
27/10/2016	Week 2	AZ111111	03/11/2017 13:48	1,500	600	3,500	750	1,050	718,950	720,000
30/10/2016	Week 2	AZ111112	15/11/2017  1:48:00 PM	-	1,500	3,000	750	1,000	119,000	120,000
Grand Total				2,400	2,280	6,850	2,050	2,050	1,657,950	1,660,000

[aarkay29 · Accepted solution]

PFA

[ngrunoz]

Thanks Aar. Am I able to create another Auto numbered field in which I can be able to use as a filter to say Like all process which happed between Date X and Y as  1 ,2 ,3 .........etc. to use as a filter and in Charts.

[aarkay29]

Can you please explain a little detail what you are trying to achieve. If you just want to make a key of Date i would suggest using NUM(DateField) as Key.

[ngrunoz]

Each year we have a different  Start date for our buying period and also is each Branch in each period.

I would like to do a period analysis and compare all period and all branches from the start of  their buying period.  not based on the calendar year as the buying trend is determined  by the number of days we have been buying in each period. I would only want to count Weekdays and non holidays in each of those period. And call start date for each branch and each Period Day 1.

[aarkay29]

If I understood correctly, in this scenario for a specific branch you want start date as Day 1 and end of the period date as No.working Days! correct??

[ngrunoz]

Correct Branch , Period Start Date As Day 1 , Day 2 ,Day 3  e.t.c to end of period. and ignoring all no working Days.

[aarkay29]

Awesome ! can you please share some sample data with more than one branch,start dates and period end dates so that I could work on and share with you

[ngrunoz]

Attached the sample Data.

I have generated Data for 2016  and some few projections for 2017. but I dint create a factor for holidays.

I would want to plot last year's sales, this year's projections and this year's Actuals on a line graph as they will be come through to compare with last year and Projection. and then on a Table running totals by branch and also by individual miner.

thanks