topic Re: sum({<Region=>}Forecast) does not work in QlikView

sum({}Forecast) does not work

Thu, 21 Jan 2016 09:50:37 GMT

Hi guys

i have a simlar appliaction like below two table

Month	Region	Prodcuct	Sales
201510	North	aa	10
201510	South	aa	5
201511	North	aa	15

Month	Prodcuct	Forecast
201510	aa	10
201511	aa	15

I use Month & Product as the foreign key to link and design a chart as below

Month	Prodcuct	Forecast	Sales
201510	aa	10	15
201511	aa	15	15

As the above example, when I select "South" in the Region

201511 data will be lost even I use sum({<Region=>}Forecast)

How to solve this problem when sales include different types but forecast only setting for the month ?

PS In my real case (much more complicated), sometimes it work,but a third measurement (Invoice) will affact by the region

Re: sum({}Forecast) does not work

Anonymous — Thu, 21 Jan 2016 11:06:45 GMT

Hi Jarven,

Here an example I've created by using your data and expression, it works fine. All expressions are dissociated with the filter Region.

Hope this will be helpful.

Best regards.

Re: sum({}Forecast) does not work

timsaddler — Thu, 21 Jan 2016 11:22:18 GMT

Have you tried to "concatenate" the 2 data sets?

That might create a better data model.

Kind regards

Re: sum({}Forecast) does not work

Anonymous — Thu, 21 Jan 2016 11:26:33 GMT

why not try only sum(Forecast)??

Re: sum({}Forecast) does not work

markodonovan — Thu, 21 Jan 2016 12:02:36 GMT

Hi Jarven,

Sounds like this should work.

I created a script as follows and used your expression sum({<Region=>}Forecast) and selected South and all seemed ok.

I have attached a sample app, you could always post a sample app with your problem.

Hope this helps.

Mark

table1:

LOAD Month&Prodcuct as %key,

Month,

Prodcuct,

Region,

Sales

FROM

[https://community.qlik.com/thread/200879]

(html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD

Month&Prodcuct as %key,

Forecast

FROM

[https://community.qlik.com/thread/200879]

(html, codepage is 1252, embedded labels, table is @2);

Re: sum({}Forecast) does not work

Fri, 22 Jan 2016 01:59:44 GMT

I know it should be work

So I was wonder why my application was affact by the filter ?

Maybe my expression is too complicated?

if(SalesType = "Type1", sum({<[Sales Document Type],[Revenue Document Type],Region>}Sales),

sum({<[Sales Document Type],[Revenue Document Type],Region>}Invoice))

Re: sum({}Forecast) does not work

Fri, 22 Jan 2016 02:01:20 GMT

I know it should be work

So I was wonder why my application was affact by the filter ?

Maybe my expression is too complicated?

if(SalesType = "Type1", sum({<[Sales Document Type],[Revenue Document Type],Region>}Sales),

sum({<[Sales Document Type],[Revenue Document Type],Region>}Invoice))