Cross table wanted

[kkkumar82]

Hi Qlikers,

I have a small requirement let me explain. I have four variables two for product and two for supplier. My requirement of showing the data is in this way.

	Old	New	Growth
Product	1500	2000	25%
Supplier	5000	4500	-11.11%

But I have used to straight table without any dimension and I got the data in a single row.

point to be noted I dont have Product, Supplier Dimensions(assumption).I am attaching a sample qvw(Wanted Dimension sheet).

[MK_QSL]

Create a Pivot Table

Calculated Dimension

=ValueList('Product','Supplier')

=ValueList('New','Old','Growth')

Expression

IF(ValueList('Product','Supplier') = 'Product' and ValueList('New','Old','Growth') = 'New', Num($(ProductNewSale),'##0') ,

IF(ValueList('Product','Supplier') = 'Product' and ValueList('New','Old','Growth') = 'Old', Num($(ProductOldSale),'##0') ,

IF(ValueList('Product','Supplier') = 'Product' and ValueList('New','Old','Growth') = 'Growth', Num(($(ProductNewSale) - $(ProductOldSale))/$(ProductNewSale),'#0.00%') ,

IF(ValueList('Product','Supplier') = 'Supplier' and ValueList('New','Old','Growth') = 'New', Num($(SupplierOldSale),'##0') ,

IF(ValueList('Product','Supplier') = 'Supplier' and ValueList('New','Old','Growth') = 'Old', Num($(SupplierNewSale),'##0') ,

IF(ValueList('Product','Supplier') = 'Supplier' and ValueList('New','Old','Growth') = 'Growth', Num(($(SupplierOldSale) - $(SupplierNewSale))/$(SupplierNewSale),'#0.00%')))))))

Check enclosed file...

[kkkumar82]

Thanks Manish for your immediate response, what I want is actually this but can we avoid the two labels Dimension1 and Dimension2 so that it looks like a cross table.

Thanks

Kiran Kumar

[MK_QSL · Accepted solution]

YES

[kkkumar82]

Thanks alot, can you share or create a document which explains how valuelist or valueloop functions works.

Kiran Kumar

[MK_QSL]

ValueList() – For those tricky situations