topic Re: Current week Vs Previous week in QlikView

Current week Vs Previous week

pavanqlik — Mon, 22 Feb 2016 09:51:12 GMT

Hi Guys,

I had a scenario, where i had a closed date field.

I am taking week from close date. when a user click on w32 in list box, i need to see columns in staright table with w32 current week and w31 previous week of same year.

If i select w1 of 2014 , then i need to see two columns with W1 2014 as one and W52 2013 as second.

I am doing sum of sales for Current and Previous week across Account Name.

Please help me in getting the expressions.

Re: Current week Vs Previous week

kaushiknsolanki — Mon, 22 Feb 2016 10:45:38 GMT

Hi,

Create a week from Date using.

Week(Date) as Week

And also have Year field created from Date

Year(Date)

Use below Expressions.

1. Sum(Sales) ---- For elected Week.

2. Sum({<Week = {"$(=week(max(Date)-7))"}>}Sales)

Regards,

Kaushik Solanki

Re: Current week Vs Previous week

swuehl — Mon, 22 Feb 2016 10:49:12 GMT

You can create a sequential field counting the weeks in your master calendar, maybe using autonumber(), then use this field in your set analysis

MasterCalendar: //DateField input records come in sorted asc

LOAD

DateField,

Autonumber(Weekstart(DateField), 'Week') as WeekNoSeq,

...

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq))'}>}Sales)

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'}>}Sales)

Re: Current week Vs Previous week

pavanqlik — Mon, 22 Feb 2016 12:51:57 GMT

Thanks for your response

I tried it,but WeekNoSql is not coming in order

Re: Current week Vs Previous week

swuehl — Mon, 22 Feb 2016 13:30:36 GMT

This might depend on the way you create your DateField.

You should be able to create a sequential order independent from load order of DatField using

Div(WeekStart( DateField), 7) as WeekNoSeq,

Re: Current week Vs Previous week

pavanqlik — Tue, 23 Feb 2016 05:07:47 GMT

Hi Swuehl,

I have closed dates as only one date that to Wednesday every week.

Re: Current week Vs Previous week

Anonymous — Tue, 23 Feb 2016 05:29:06 GMT

might be helpful for you?

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Re: Current week Vs Previous week

tresB — Tue, 23 Feb 2016 05:33:53 GMT

Could you post a sample qvw?

Re: Current week Vs Previous week

HirisH_V7 — Tue, 23 Feb 2016 06:43:12 GMT

Hi,

May be like this,

I have taken a sample data,

Create a Variable,

=max({<Flag={'week'} , Year={'$(=Max(Year))'} >}Week)

In straight Table,

=IF( vWeek=1,(Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek)}>}Sales)
-
Sum({<Flag={'week'} , Year={'$(=Max(Year)-1)'},Week={53}>}Sales)),


(Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek)}>}Sales)
-
Sum({<Flag={'week'} , Year={'$(=Max(Year))'},Week={$(=vWeek-1)}>}Sales))

)

There by ,When you select week 1 , condition is set to pick previous year max(Week) and if other means 2 weeks current and previous week.

Hope this helps,

PFA,

Hirish

Re: Current week Vs Previous week

jagan — Tue, 23 Feb 2016 07:01:32 GMT

Hi Pavan,

Check this links for similar type of expressions

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Regarsd,

Jagan.Current week Vs Previous week

Re: Current week Vs Previous week

swuehl — Tue, 23 Feb 2016 08:49:50 GMT

I have closed dates as only one date that to Wednesday every week.

This should work also with only a date record per week. Didn't it work using Div() function and Weekstart()?

Just take care to clear selections in calendar field when querying the previous week, to avoid incompatible set assignments. So when the user may select in CloseDate, you need to clear field CloseDate:

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate= >}Sales)

and all other calendar fields that the user may select in.

Re: Current week Vs Previous week

PradeepReddy — Tue, 23 Feb 2016 09:53:18 GMT

i think we have to use CEIL function as well, like bellow...

Ceil(WeekStart(Date_Field)/7) as WeekSeq

EX:

Ceil(Weekstart(Date#('1900-1-1','YYYY-MM-DD'))/7) returns 1

DIV(Weekstart(Date#('1900-1-1','YYYY-MM-DD')),7) returns 0

Re: Current week Vs Previous week

swuehl — Tue, 23 Feb 2016 10:40:56 GMT

I believe there is just a constant offset of 1 for all weeks between these two functions. Doesn't really matter IMO.

Re: Current week Vs Previous week

pavanqlik — Tue, 23 Feb 2016 12:07:04 GMT

Hi swuehl,

I am able to generate seqnumber correctly, but when i am writing expression =Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate= >}Sales) for prev week its not working

when using single quotes in expression its not working , but when i removed its working fine but am having a issue here

If i select Year,Quarter and Week filters in my dashboard i am able to see current week information in st table but prev week is showing as 0

But when i unselect week selection and when i make selection in seqnumber list box iam able to see both current week and prev week in st table.

Could you please help me on this.....

Re: Current week Vs Previous week

swuehl — Tue, 23 Feb 2016 20:24:39 GMT

As I tried to explain above, you need to take care the the field modifications made in the set expression are compatible with user selections. If you make selections in field Week, this selection will be incompatible with a field modifier trying to select the previous WeekNoSeq. Hence you need to clear the user selections in calendar fields in the set expression:

=Sum({<WeekNoSeq = {'$(=Max(WeekNoSeq)-1)'},CloseDate=, Week=, Year=, Quarter= >}Sales)

[add more calendar fields as required.]

Re: Current week Vs Previous week

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

Hi, I have simmilar problem due to values from the previous week in reference to the next (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 work 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?