32 Replies Latest reply: Jan 19, 2016 9:38 AM by Андрей Шепель

# Week index number

Good day!

Colleagues, tell me please, has QV some function to get index number of values in some field (not in load script)

Model and necessary WeekNumber in attached file.

Thanks.

• ###### Re: Week index number

Can you provide the output also in excel?

• ###### Re: Week index number

Excel has field WeekNumber..

• ###### Re: Week index number

So you want to derive WeekNumber for YearWeek?

• ###### Re: Week index number

Yes. By some function in expr.

• ###### Re: Week index number

Like this?

• ###### Re: Week index number

Yes.

but RowNo get row number of field which is set in dimensions.

in need to calculate sum for last 13 weeks, that's why i need calculate number index of all YearWeeks.

• ###### Re: Week index number

Either Create YearWeekID in Script using AutoNumber function

or

Use set Analysis for getting last 12/13 weeks...

either provide sample data or sample app to work

• ###### Re: Week index number

In attached model i need calculate sales for last 14 weeks by set analysis.

• ###### Re: Week index number

In what context? In that table, weekly, or for a selected compare date in a text box?

• ###### Re: Week index number

Hello!

you can use recno() and expression like sum({<Rec = {">=\$(=max(total Rec)-14) "}>}Sales).

See example

• ###### Re: Week index number

But i need calculate sum without changing script (model has more 3 hours for reload)

• ###### Re: Week index number

ok,

try this expression

if(sum(total if(left(YearWeek,4)=left(max(total YearWeek),4),1,0))<14,

sum( {<YearWeek = {">\$(=max(total if(left(YearWeek,4)<>left(max(total YearWeek),4),YearWeek ))-14+sum(total if(left(YearWeek,4)=left(max(total YearWeek),4),1,0))) "}>}

Sales)

, sum( {<YearWeek = {">\$(=max(total YearWeek) -14)"}>}Sales)

)

It's difficult, but the only thing that I thought of

• ###### Re: Week index number

Hi ,

can you provide your required Output,

Inter-Record Functions

Check this,

Hirish

• ###### Re: Week index number

My required output in attached model

• ###### Re: Week index number

Hi ,

Hope this is what you required,

Two Dimensions:

=Aggr(Mid(YearWeek,1,4),YearWeek)  as Year

=Aggr(Mid(YearWeek,5,6),YearWeek)  as week

Expression:

=If(YearWeek>='201540',Sum(Sales))

Output:

Hope this helps,

PFA,

Hirish

• ###### Re: Week index number

In your example, if add YearWeek 201602, we should to change expression If(YearWeek>='201541',Sum(Sales))

So, we always should to change expression manually, the chart is not automatic

• ###### Re: Week index number

Hi,

Yes,It will be after '201601' right.I think new entries are required.

or Else

we can write conditional expression like this,

```=If(YearWeek>='201540' and YearWeek<='201601',Sum(Sales))

```

Hope this helps,

Hirish

• ###### Re: Week index number

Dear colleagues, thanks for your help!

My charts must be calculated on automatic base.

• ###### Re: Week index number

If you can change load script, use recno(), else try to use expression in my early answer.

No function to calculate index number field that can be used in expression

• ###### Re: Week index number

ok. if i use recno(), it will return me number of each record.

For example:

YearWeek          Sales  RecNo()

201501                1          1

201501               67         2

201501               24         3

201502               24         4

I need:

YearWeek          Sales  RecNo()

201501                1          1

201501               67         1

201501               24         1

201502               24         2

• ###### Re: Week index number

so, you have records with repeatly YearWeek and some YearWeek can be missed?

• ###### Re: Week index number

for example use script

DATA:

Sales

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1)

;

left join (DATA)

, RowNo() as Row

Resident DATA;

and expression sum({<Row= {">\$(=max(total Row)-14) "}>}Sales)

• ###### Re: Week index number

i've solved it:

DATA:

Sales

FROM

[Test.xlsx]

(ooxml, embedded labels, table is Source1)

;

YearWeeks_Temp:

YearWeek

Resident DATA;

YearWeeks:

RowNo() as WeekNumber

Resident YearWeeks_Temp

Order By YearWeek asc;

DROP Table YearWeeks_Temp;

Left Join(DATA)

*

Resident YearWeeks;

DROP Table YearWeeks;

• ###### Re: Week index number

I think it's not optimized

• ###### Re: Week index number

Hi,

By this you will get the 2016 first week as 53 ,is it correct!!!!

-Hirish

• ###### Re: Week index number

In some country number of week determine like:

Every Sunday begins a new calendar week;

January 1, regardless of the day of the week, starting the first calendar week.

• ###### Re: Week index number

Friends...

This field i need to get not as WeekNumber (as usual, every year has 52 weeks), but for calculating sales for last 14 weeks

• ###### Re: Week index number

Hi ,

```Data:
Mid(YearWeek,1,4) as Year,
Mid(YearWeek,5,6) as week ,
YearWeek,
Sales
FROM
[Test.xlsx]
(ooxml, embedded labels, table is Source1) where YearWeek>'201540';
```

Then in front end you ,

expression:

=Sum(Sales)

Hope this helps,

.PFA,

Hirish

• ###### Re: Week index number

How can we automatic determine number YearWeek - 14, we can't write 201540