Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How to show budget for unsold item ?

Hi every Body  ,
I need your help please , I have three dimension tables : Items  , Country and Budget

For all Item we have a budget by country.

My Problem is  :

I have 3 items (It1 , It2 and It3) and 2 countries ( France , US ).

Suppose for France , I sold only It1 and It3. in my table , I have :

CountryItemTurnoverBudget
FranceIt135 €30 €
FranceIt225 €20 €

In my case I'll like to have this table :

CountryItemTurnoverBudget
FranceIt135 €30 €
FranceIt225 €20 €
FranceIt30 €20 €

Is it possible ? if yes how I can do  please ?

8 Replies

Re: How to show budget for unsold item ?

How is budget of 20 getting assigned for It3?

Not applicable

Re: How to show budget for unsold item ?

in the Budget table  , for all iTem there is a assigned budget.

I want to see this budget even if the item is not sold.

Re: How to show budget for unsold item ?

I guess I don't know how your data is structured, would you be able to provide a sample?

Not applicable

Re: How to show budget for unsold item ?

The ID_Budget is a composite key :

ID_Budget ==> ID_Country&'-'&ID_Item

Capture d’écran 2016-05-05 à 20.14.44.png

Re: How to show budget for unsold item ?

How have you included the budget values into your data model?

The budget rows should be concatenated not joined to your table.

Use Concatenate with the same dimensions and a new column for BudgetAmount .

That way when you sum through the table by dimension, the Turnover totals and Budget totals will work.

You may need to add your Budget values against a dummy date of the 1st or last day of the month so your date dimensions work.

Re: How to show budget for unsold item ?

Not applicable

Re: How to show budget for unsold item ?

Hey Collin , the budget is in separate table.
In the budet Table , I create a composite key : ID_Budget = ID_Country&'-'&ID_Item.
I create the Same key in my Fat_Table , so My Bugets Table is automatically linked with my FactTables thank ID_Budget column.

Capture d’écran 2016-05-05 à 20.14.44.png

Partner
Partner

Re: How to show budget for unsold item ?

This post may help...

CountryMaster:

LOAD * INLINE [

    Country

    A

    B

];

ItemMaster:

LOAD * INLINE [

    Item

    It1

    It2

    It3

];

BUDGET:

LOAD * INLINE [

    Country, Item, Budget

    A, It1, 500

    A, It2, 300

    A, It3, 100

];

SalesFACT:

NoConcatenate

LOAD * INLINE [

    Country, Item, Value

    A, It1, 100

    A, It3, 50

];

Concatenate

LOAD * Resident BUDGET;

Drop table BUDGET;