topic Straight Table with Custom Dimensions in QlikView

Straight Table with Custom Dimensions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Straight Table with Custom Dimensions

Anonymous — Sat, 16 Apr 2016 10:34:23 GMT

Hi DRJ,

Could you please post some sample data to understand better.

Regards,

-- Karla

Re: Straight Table with Custom Dimensions

marcus_sommer — Sat, 16 Apr 2016 11:43:57 GMT

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

Re: Straight Table with Custom Dimensions

Anonymous — Sat, 16 Apr 2016 15:38:18 GMT

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

Re: Straight Table with Custom Dimensions

Anonymous — Sun, 17 Apr 2016 18:59:31 GMT

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

Re: Straight Table with Custom Dimensions

Anonymous — Sun, 17 Apr 2016 19:13:04 GMT

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.

Re: Straight Table with Custom Dimensions

Mon, 18 Apr 2016 01:24:05 GMT

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.

Re: Straight Table with Custom Dimensions

Anonymous — Mon, 18 Apr 2016 19:19:55 GMT

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

Re: Straight Table with Custom Dimensions

qliksus — Tue, 19 Apr 2016 04:09:42 GMT

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

Re: Straight Table with Custom Dimensions

marcus_sommer — Tue, 19 Apr 2016 04:19:26 GMT

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

Re: Straight Table with Custom Dimensions

sateeshkumar — Thu, 25 May 2017 10:23:37 GMT

Hi,

Did you find answer for this as i am facing the same issue. I have one inline and also have year_month as dimentions. So i want to pivot table with last 6 months data. whereas each row in inline column having different expression. When i tried to develop this by using pick(match...) condition, it is displaying only latest month.All other months having null values.

below is my expreesion for one row.

pick(match(dim_1,'value1','value2',...),

Only({<Date={"<=$(vMaxDate)>=$(vMin6month)"}>}metric1),

Only({<Date={"<=$(vMaxDate)>=$(vMin6month)"}>}metric2)...)

So please help me to solve this issue.

Thanks