topic Re: Help with ignoring a field in QlikView

Help with ignoring a field

Tue, 30 Sep 2014 14:52:25 GMT

Is there a way to ignore a field? I had two tables and I joined them into one to be able to do a side by side comparison. For example, I have a two date fields in a table now. One is FutureDate and one is PastDate.

FutureDate (i.e 201440, 201441) is for forecasts in the future for 20 weeks.

PastDate (201340, 201341) is for sales in the past for 2 years back

I used the 'Future Date-100 as PastDate' in order to be able to do side by side comparison.

But now I need the Sales with its original date ... what is the best way to go about doing this? Changing the way it is modeled in the script or using a set analysis?

All I need is 4 weeks of actual sales from current week and 10 weeks of future forecasts in a graph. So if current week was 201440, I would need actual sales for 201439, 201438, 201437, and 201436 and then I would need forecast for 201440 to 201450 in one graph.

Any suggestions? or advice?

Re: Help with ignoring a field

geert_gelade — Wed, 01 Oct 2014 04:55:42 GMT

One possible solution is to put both sales and forecast in one table with only one date-field.

In your graph you can use something like this to show only the periods you want:

Sales: if(Period >= $(vCurrentWeek)-4 and Period <= $(vCurrentWeek) ,sum(Sales),0)

Forecast: if(Period > $(vCurrentWeek) and Period <= $(vCurrentWeek)+10,sum(Forecast),0)

Please see attached qvw.

Re: Help with ignoring a field

Wed, 01 Oct 2014 13:59:12 GMT

Would this work if I set my vCurrentWeek = field in my table?

For example, I have a 'snapshot week' field in my table which is always the current week and it is in the Monday date format....Can I set vCurrentWeek= 'snapshot week'?

Re: Help with ignoring a field

geert_gelade — Thu, 02 Oct 2014 06:40:39 GMT

I think it will work, yes.

If not, you can upload a sample of your data.

Re: Re: Help with ignoring a field

Thu, 02 Oct 2014 21:07:29 GMT

Can you see if you can fix this app? The requirement is that need to be able to see 5 weeks of historical forecast from current week and 10 weeks of future forecast from current week. As well as 5 weeks of actual from current week.

If current week = 9/1/2014

I will need historical forecast for weeks: 8/25, 8/18, 8/11, 8/4, 7/28

I will need actual sales for weeks: 8/25, 8/18, 8/11, 8/4, 7/28

and I want to see future forecast for current week: 9/1 and 9/8, 9/15, 9/22, 9/29, so forth until 11/10

Re: Re: Re: Help with ignoring a field

geert_gelade — Fri, 03 Oct 2014 06:42:28 GMT

Is this the result you need ?

In your app, I think you're mixing up weeks and dates.

I changed the expression for Sales to:

if([calendar_week]>= vCurrentDate- (5*7) and [calendar_week] <= vCurrentDate ,sum(Sales),0)