topic Re: Week index number in QlikView

Week index number

Anonymous — Tue, 19 Jan 2016 09:13:00 GMT

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

MK_QSL — Tue, 19 Jan 2016 09:14:04 GMT

Can you provide the output also in excel?

Re: Week index number

Anonymous — Tue, 19 Jan 2016 09:15:57 GMT

Excel has field WeekNumber..

Re: Week index number

MK_QSL — Tue, 19 Jan 2016 09:17:09 GMT

So you want to derive WeekNumber for YearWeek?

Re: Week index number

Anonymous — Tue, 19 Jan 2016 09:18:49 GMT

Yes. By some function in expr.

Re: Week index number

MK_QSL — Tue, 19 Jan 2016 09:21:09 GMT

Like this?

Re: Week index number

Anonymous — Tue, 19 Jan 2016 09:24:26 GMT

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

MK_QSL — Tue, 19 Jan 2016 09:28:32 GMT

Either Create YearWeekID in Script using AutoNumber function

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

either provide sample data or sample app to work

Re: Week index number

Anonymous — Tue, 19 Jan 2016 09:33:10 GMT

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

Re: Week index number

jonathandienst — Tue, 19 Jan 2016 09:51:44 GMT

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

Re: Week index number

Anonymous — Tue, 19 Jan 2016 09:58:59 GMT

In that table. By weeks.

Re: Week index number

t_chetirbok — Tue, 19 Jan 2016 10:08:10 GMT

Hello!

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

See example

Re: Week index number

HirisH_V7 — Tue, 19 Jan 2016 10:32:10 GMT

Hi ,

can you provide your required Output,

Inter-Record Functions

Check this,

Hirish

Re: Week index number

Anonymous — Tue, 19 Jan 2016 10:50:04 GMT

Thanks for your solution.

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

Re: Week index number

Anonymous — Tue, 19 Jan 2016 11:06:58 GMT

My required output in attached model

Re: Week index number

t_chetirbok — Tue, 19 Jan 2016 11:35:03 GMT

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

HirisH_V7 — Tue, 19 Jan 2016 12:19:36 GMT

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

t_chetirbok — Tue, 19 Jan 2016 12:36:33 GMT

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

HirisH_V7 — Tue, 19 Jan 2016 12:46:01 GMT

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

Anonymous — Tue, 19 Jan 2016 12:56:37 GMT

Dear colleagues, thanks for your help!

My charts must be calculated on automatic base.

Maybe your give me advice how to get YearWeek index number in load script?