topic Re: set analysis formula in QlikView

set analysis formula

Mon, 08 Sep 2014 16:14:17 GMT

can you use set analysis to look at only certain parts of a field?

For example, I have the calendar dates as 201438, 201439, etc... for forecasts but I also want to show sales.

what expression can I use to say only look at the sales for week 38 from year 2013...

Re: set analysis formula

Mon, 08 Sep 2014 16:15:44 GMT

Do you have the same kind of date format tied to sales as well?

Re: set analysis formula

Mon, 08 Sep 2014 16:21:07 GMT

Yes Suraj

Re: set analysis formula

Mon, 08 Sep 2014 16:27:31 GMT

Can you try this and let me know if this doesn't works. Sum({$<Dates={'201338'}>}Sales)

Thank you

Suraj

Re: set analysis formula

Mon, 08 Sep 2014 16:38:46 GMT

It does not work.

The problem is that the forecasts are future dates, so 201442, 201443, and so on...while Sales are in past dates so 201339, 201348..and so on.

I need forecast and sales side by side...

Re: set analysis formula

Mon, 08 Sep 2014 16:49:16 GMT

I see what you are saying now. If you can upload a sample document I can do it for you.

Thanks

Suraj

Re: Re: set analysis formula

Mon, 08 Sep 2014 16:52:13 GMT

Here is an example document of a similar solution.

Thank you

Suraj

Re: Re: set analysis formula

Mon, 08 Sep 2014 17:05:31 GMT

here is an example, thanks for your help

Re: Re: Re: set analysis formula

Mon, 08 Sep 2014 17:23:13 GMT

Check this and let me know if that is what you are looking for.

Thank you

Suraj

Re: set analysis formula

Mon, 08 Sep 2014 17:48:55 GMT

This is not working right. I don't see any of my forecast dates (i.e 201431,201432)

Re: set analysis formula

Mon, 08 Sep 2014 18:04:18 GMT

Can you post an excel output how it should show please?

Thanks

Suraj

Re: Re: set analysis formula

Mon, 08 Sep 2014 18:11:12 GMT

Check the first tab in the excel spreadsheet called 'final layout'

Re: Re: Re: set analysis formula

Mon, 08 Sep 2014 18:34:12 GMT

Check this QVW. If this is what you are looking for.

Thank you

Suraj

Re: Re: Re: Re: set analysis formula

Mon, 08 Sep 2014 18:55:34 GMT

This is ok... Is there any other way to change the set analysis in the expression so that the year is not hardcoded?

Because in 2015, they would want to see 2014 and 2013... How could you change the set analysis so that it looks at the max year in the forecast and selects a year?

So if you had something like this, it would show as below

Week	Brand	Product	Retailer	Forecast	Sales 1 yr ago	sales 2 yr ago
201451	Low	B	LAX	100	150	200
201452	Low	B	LAX	200	250	300
201501	Low	B	LAX	300	350	400
201502	Low	B	LAX	400	450	450

the ones in bold would be from year 2013 and 2012.. while the underlined ones would be from 2014 and 2013...

Re: Re: Re: Re: Re: set analysis formula

Mon, 08 Sep 2014 20:06:00 GMT

Check this.

Thanks

Suraj

Re: set analysis formula

lucianosv — Mon, 08 Sep 2014 20:10:03 GMT

You can do a LEFT JOIN using Table Forecast with itself in the script by keyword:

(Week - 100) the Week, Product, Brand and Retailer

Re: set analysis formula

Wed, 10 Sep 2014 18:22:08 GMT

Suraj,

What if my sales dates were in the Monday date format... for example... 8/5/2013, 8/12/2013, 8/6/2012, 8/13/2012..etc but my forecast was still in format yyyyww (i.e 201437, 201428, etc)

How would this change my application?

Re: set analysis formula

Wed, 10 Sep 2014 18:37:30 GMT

I would convert dates to their respective week. Like (Year(SalesDate) & week(SalesDate)).

Thanks

Suraj

Re: set analysis formula

Wed, 10 Sep 2014 21:45:34 GMT

Luciano,

Would (Week-200) the Week, Product Brand and Retailer give me Sales from 2 years ago? or would I need to do something differently?

Re: set analysis formula

Thu, 11 Sep 2014 16:38:28 GMT

(Week - 100) the Week, Product, Brand and Retailer

the top works for when you need it for a year before....what can I do to make get the actual sales for 2 years before?

If I do (Week-200), it's giving me the same results as the (week-100)

Is there some other set analysis that I could use?