topic Re: Cross table wanted in QlikView

Cross table wanted

kkkumar82 — Fri, 24 Jul 2015 10:30:10 GMT

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

Re: Cross table wanted

MK_QSL — Fri, 24 Jul 2015 10:56:39 GMT

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

Re: Cross table wanted

kkkumar82 — Fri, 24 Jul 2015 11:16:06 GMT

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

Re: Cross table wanted

MK_QSL — Fri, 24 Jul 2015 11:29:41 GMT

YES

Re: Cross table wanted

kkkumar82 — Fri, 24 Jul 2015 11:37:56 GMT

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

Kiran Kumar

Re: Cross table wanted

MK_QSL — Fri, 24 Jul 2015 11:42:34 GMT

ValueList() – For those tricky situations