Straight Table with Custom Dimensions

[Anonymous]

Hi everyone,

I need to create a Straight Table as shown below:

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Totals
Values Predicted	10.00	20.00	30.00	35.00	54.00	24.00	32.00	23.00	34.00	12.00	19.00	18.00	311.00
Actual Values	10	20	30										60
Current Mth Values				3	3	3	3	3	3	3	3	3	27
Future Plan 1	3	4	3	2	1	5	4	6	4	3	3	2	40
Future Plan 2
Delivered Charges													0
2016 Forecast
ABC

----------------------------

Issues in building this view:

- The Dimensions are not present in any table. So I have to create them explicitly. Which is the best way to do it? Also explain how.

- I can create Jan, Feb, Mar....Dec columns by creating expressions. But the problem is, not all values are stored in the same month column. For example, for Values Predicted row, the values for every month are present in a different fields. Similarly, for the row Actual Values row, the values are coming from different fields. If I create an expression for Jan month as sum(val_jan), it will create values for one row, but what about the rest of the rows?

-Somehow if we create the dimensions explicitly, how are they mapped to the monthly values?

I would really appreciate if someone goes through the caveats and guide me through proper solution.

Thanks

[Anonymous]

Hi DRJ,

Could you please post some sample data to understand better.

Regards,

-- Karla

[marcus_sommer]

You need a synthetic dimension created per loosen table within the script or per valuelist() within the chart and your chart-expressions must be related on them respectively query which dimension-value belonged to the actual row. Here a small example with a table:

Dim:

Load * Inline [

Dim

Values Predicted

Actual Values

…

];

Chart-Expressions: (12 - for each column und there changing the Month within the set analysis)

Pick(

     match(Dim, 'Values Predicted', 'Actual Values', ….),

     sum({< Month = {'Jan'}>} Predicted),

     sum({< Month = {'Jan'}>} Actual),

     ….)

- Marcus

[Anonymous]

Hi Karla,

I can't post the sample data as the original data is too complex and it's really hard to mimic that model.

Values Predicted

Actual Values

Current Mth Values

Future Plan 1

Future Plan 2

Delivered Charges

2016 Forecast

ABC

These values should be in the First column. They are not present in any Column of any table. So I have to create an inline table to get these values. But then, how do I map these to the values that are coming in Jan, Feb, Mar....Columns?

Also,

Values Predicted has it's own Columns from Jan to Dec where I need to pull the values.

Actual Values has it's own Columns from Jan to Dec where I need to pull the values.

When I do this: sum(Jan1) in Expr 1, sum(Feb1) in Expr2.... I get the same values across all the columns. I need to get different values for each row...

[Anonymous]

Hi DRJ,

Based on your previous comment, means that you're pulling data from different sources, but you have data per month on each of them,  which is the one that you want to gather in a single table. If that so, you could add a common column on each source containing the name describing that information as use this as your dimension.

I'm attaching an excel file, that I have used to create this data structure, based on what I have understand so far, and a qvw with an example of how could you load the data.

I hope this could help you.

Regards,

-- Karla

[Anonymous]

Hi Karla Rivas,

Thank you so much and I really appreciate you for taking time and creating this demo view. I see some light in this answer and will try to implement the same method on my data sets once I go back to work. I will let you know how it goes. Thank you once again.

[#7]

You may have 3 different methods:

1. Synthetic Dimensions with value List , Pick & Match functions

2. Add the data island dimension table

3. Add the new dimension table that associates to your data model.

[Anonymous]

Can I not use Sum or If conditions in Load Scripts and resident data?

[qliksus]

That would complicate it further You data has each month in a separate column so use something like a Crosstable to convert it into a single column and make your chart something like this

Dimension : Valuelist('Values Predicted','Actual Values','...........)

Dimension2 : Month

Exp :  if(  Valuelist('Values Predicted','Actual Values','...........) = 'Values Predicted',  exp1 ,

                         if(  Valuelist('Values Predicted','Actual Values','...........) = 'Actual Values',  exp2,  .............)

Or you may use an inline table to achieve what you want

[marcus_sommer]

Of course, you could (pre-)calculate these within the script but you will loose some (or all) flexibility within the script - it means the ability from the table to react on further selections like products, regions or similar.

Have you tried the suggestions with a loosen dimension-table or a synthetic dimension per valuelist like above mentioned?

- Marcus