sum({<Region=>}Forecast) does not work

[#1]

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

[Anonymous · Accepted solution]

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.

[timsaddler]

Hi

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

That might create a better data model.

Kind regards

[Anonymous]

why not try only sum(Forecast)??

[markodonovan]

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);

[#6]

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))

[#7]

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))