Convert Straight Table to an Aggregated Table

[dapostolopoylos]

Hello, everyone.

My problem is this:

I have created a really large straight table with many dimensions (many of them calculated) and many expressions.

This table works more like an Excel table rather than a QlikView straight table.

The expressions rely heavily on the other columns through references and there is a lot of business logic in them (if this then that or...). 

Nevertheless the numbers are ok and business aproves them and now they want me to create the same table on company level by year and month... and it seems that i cannot make the numbers be correct in the new table...

The expression that bugs me most is this

=If(Category='Consumption' and [Actual Quantity]=0,
0,
if(Category='Production',
Null(),
if(ProducedMaterialGroupCode='90014' or NoCostFlag=1 or ProducedMaterialCode='2280249',
0,
[Theoretical Requirement]+[Actual Quantity]
)
)
)

where [Theoretical Requirement]and[Actual Quantity]are other columns of the table.

If it helps what i want to do is shown below

Any ideas?

[vamsee]

May be try 

=Sum(Aggr(Sum({<Category-={'Consumption', 'Production'},[Actual Quantity]-={0},ProducedMaterialGroupCode={'90014'}, NoCostFlag-={1} , ProducedMaterialCode-={'2280249'}>}[Theoretical Requirement]+[Actual Quantity]), Company_Code))

If this doesn't work, I would share your qvw for people to look at your calculated dimensions.

[dapostolopoylos]

Thank you for your reply but it doesn't work...

Theroretical Requirement is itself calculated and i cannot exclude both 'Consumption' and 'Production' from Category, these are all the values of the field...

Nevertheless, thank you, i wlli play around a little with your central idea of Aggr

[sunny_talwar]

You can also do this

Sum(If(Category = 'Consumption' and [Actual Quantity] = 0, 0,
If(Category = 'Production', Null(),
If(ProducedMaterialGroupCode = '90014' or NoCostFlag = 1 or ProducedMaterialCode = '2280249', 0, RangeSum([Theoretical Requirement], [Actual Quantity])))))

or just create a flag in the script if possible (i.e. if all the fields are located in a single table)

LOAD *,
If(Category = 'Consumption' and [Actual Quantity] = 0, 0,
If(Category = 'Production', Null(),
If(ProducedMaterialGroupCode = '90014' or NoCostFlag = 1 or ProducedMaterialCode = '2280249', 0, 1) as CalcFlag

and then this

Sum({<CalcFlag = {1}>} RangeSum([Theoretical Requirement], [Actual Quantity]))

[dapostolopoylos]

Hello, @sunny_talwar 

I tried the first sol;utiom you provided but unforunately it didn't  work...

I remind you that [Theroretical Requirement] and [Actual Quantity] are expression columns that i don't want them to be show in my aggregated new chart.

To make more clear what i am talking about, i'm attaching a sample qvw that contains the actual table i already created and the aggregated one that i want to get right...

I hope it will help.

[sunny_talwar]

May be this for Jan

Sum({<PostingMonth = {1}>}Aggr(
If(Category='Consumption' and Sum(Quantity)=0,
	0,
	if(Category='Production',
			Null(),
			if(ProducedMaterialGroupCode='90014' or NoCostFlag=1 or ProducedMaterialCode='2280249',
				0,
				(If(Category='Consumption' and Sum(Quantity)=0,
	0,
	if(Category='Consumption' and MaterialCodeGroup='21',
		Qty101_102,
		(([BOM Requirement]/BaseQuantity)*Qty101_102)
	)
))+Sum(Quantity)
			   )
   )
)
,PostingMonth,ProductionOrder,ComponentCode, ProducedMaterial, ProductionBatch, Component, Category))

[dapostolopoylos]

Thank you so much for your effort @sunny_talwar !

There are 8 material categories in my data model and your expression works perfectly for the 6 of them.

Is there a chance that you might take a look on those two left behind also?

I am attaching another sample file with data from those 2 categories (Packaging Materials, Additional Materials)

Thank you again! 🙂

[dapostolopoylos]

No luck with that @sunny_talwar ?

Maybe,  @swuehl , @Gysbert_Wassenaar  @tresesco , @Kushal_Chawda , @alexandros17 , do you have any ideas?

[Brett_Bleess]

You may find the following Design Blog useful in relation to the use of AGGR in Set Analysis:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

[dapostolopoylos · Accepted solution]

Thank you for response, it's funny but today i finally figured it out!

@sunny_talwar guided me to the right path and the only addition to his solution, according to the sample(3).qvw file is the following

Sum({<PostingMonth = {1}>}Aggr(
If(Category='Consumption' and Sum(Quantity)=0,
	0,
	if(Category='Production',
			Null(),
			if(ProducedMaterialGroupCode='90014' or NoCostFlag=1 or ProducedMaterialCode='2280249',
				0,
				(If(Category='Consumption' and Sum(Quantity)=0,
	0,
	if(Category='Consumption' and MaterialCodeGroup='21',
		Qty101_102,
		(([BOM Requirement]/BaseQuantity)*Qty101_102)
	)
))+Sum(Quantity)
			   )
   )
)
,PostingMonth,ProductionOrder,ComponentCode, ProducedMaterial, ProductionBatch, Component, Category,Plant,StorLoc))

Just added the last two dimension (Plant, StorLoc) in the Aggr function and everything worked!