topic Re: set analysis: Previous Week in QlikView

set analysis: Previous Week

Wed, 18 Apr 2012 20:29:59 GMT

Hi,

I did a formula with a set analysis: It's working well (good news)!!

=Sum ({<[Fiscal Year-Week]=[Current Fiscal Year-Week]>} Metric)

Now, I need to do the same but for the previous week.

Current Fiscal Year-Week contain 1 value with the current week not related to other data.

Have you got some ideas?

Thanks,

Eva

Re: set analysis: Previous Week

swuehl — Wed, 18 Apr 2012 20:37:57 GMT

What is the format of your Fiscal Year-Week field? If you use a sequential number over all years, you can probably just subtract 1 from your Current Fiscal Year-Week.

set analysis: Previous Week

Wed, 18 Apr 2012 20:46:58 GMT

My format is YYYY/WW.

Re: set analysis: Previous Week

swuehl — Wed, 18 Apr 2012 20:58:18 GMT

Try something like (untested dollar sign expansion, but the idea should be clear?):

=Sum ({<[Fiscal Year-Week]= {'$(=year(today()-7)&'/'&week(today()-7))'}>} Metric)

Re: set analysis: Previous Week

Thu, 19 Apr 2012 14:01:39 GMT

Hi,

Thanks for your answer, but I cannot use week (today()) because my fiscal week number is not align with the civil week number

Re: set analysis: Previous Week

swuehl — Thu, 19 Apr 2012 15:00:51 GMT

Try incoporating your fiscal week computation logic into above dollar sign Expansion.

Or introduce an additional calendar week field.

Or use a selection in field date with an advanced search on dates between week start of today -7 date and week end of today -7.

Re: set analysis: Previous Week

Fri, 20 Apr 2012 20:32:11 GMT

If you can change your data model, I would recommend swuehl method of having sequential numbers for weeks. If you make the number sequence in such a way that the current week is always 0, previous week is -1 (or 1 if you don't like negative numbers) then the calculation is easier. That way, your set analysis is simple, e.g., current week is Sum ({<[Fiscal Year-Week Num]={0}>} Metric) and previous week is Sum ({<[Fiscal Year-Week Num]={-1}>} Metric)

Re: set analysis: Previous Week

natalia_sterniak — Fri, 06 Dec 2019 12:48:06 GMT

Hi, I have simmilar problem due to values from the previous week in reference to the next one (not current, as I have future data)

I just need to display value from the previous week in each column, details in the picture

The week format is 'YY-WW', but i also added autonumbering.

As far I tried to do that with set anylysis like

Sum({ <master_calendar_week= { '$(vPreviousWeek)' }>} [Wartość]) where vPreviousWeek =Right(year(imputationDate),2)&'-'&(week(imputationDate)-1)

and

=Sum({<[master_calendar_week_Flag]={"$(=([master_calendar_week_Flag]-1))"}>} [Wartość]) where master_calendar_week is autonumbered week

but both of them don't work

The only formula which works it is:

=Sum({<[master_calendar_week_Flag]={"$(=max([master_calendar_week_Flag]-1))"}>} [Wartość])

and it displays value for max week-1 correctly, but only for this one and affect other measures.....

Can anybody help me with that one, please?

Regards