topic Re: Pivot Table Issue in QlikView

Pivot Table Issue

Tue, 21 Jul 2015 06:56:01 GMT

Hi team,

Kindly help me get the following output

I have a pivot table with the following fields

Input Date
Category Name	Product Name	Orders
Jewellery	Ear ring, Ring	20
Watches	Rado, Fossil	10
Electronics	Mono Pad, MINI Pad	30

Issues :
1) I need to calcuate the actual Order contribution against the Category, which is as of giving me a Group result (i.e. 20,10,30)
2) I am not able to sort the Orders (I have tried adding the expression and doing ascending sort, but no luck)

Output Data
Category Name	Product Name	Order	Orders - Actual result
Jewellery	Ear ring, Ring	20/100	0.2
Watches	Rado, Fossil	10/100 0.1
Electronics	Mono Pad, MINI Pad	30/100	0.3

Thanks in advance

Re: Pivot Table Issue

Anonymous — Tue, 21 Jul 2015 07:08:04 GMT

Hello,

1. Check aggr(), if I understand correctly. Maybe you send your app. On which level you want to see the aggregation? On product level, on category level, or product level divided by category (% contribution)?

2. Check aggr() again for sort purposes. Sorting is tricky in pivot table but can be solved via proper aggr() statements.

Serhan

Re: Pivot Table Issue

Tue, 21 Jul 2015 07:14:29 GMT

Hi Serhan,

Thanks for the quick reply.

Sorry for missing to provide the aggr level.

I want to aggr on all the above mentioned levels :

1. On product level

2. on category level,

3. Product level divided by category

Thanks

Re: Pivot Table Issue

Anonymous — Tue, 21 Jul 2015 07:24:28 GMT

Hello,

1. Product level = sum(Orders)

2. Category level = sum(total <Category> Orders)

3. 1/ 2

Serhan

Re: Pivot Table Issue

Tue, 21 Jul 2015 12:38:35 GMT

Hi Serhan,

Thanks for your inputs. but this seems not to suffice me requirement.

I need to get the % Category for the Orders (just like 20/100=0.2% contribution and so on)

Category Name	Order	Orders - Actual result
Jewellery	20/100	0.2
Watches	10/100 0.1
Electronics	30/100	0.3

As of my current exp is, which is not giving me the required result. can you look into it and provide your inputs.

=Count({1<FACT={'A'}, FLAG={'1'}, ORDERRDATE={'>=$(vFromDate)<=$(vToDate)'}>}ORDER)

Count({<FACT={'A'},FLAG{'1'},ORDERDATE={'>=$(vFromDate)<=$(vToDate)'>}ORDER)

Thanks,

Re: Pivot Table Issue

Anonymous — Tue, 21 Jul 2015 12:41:55 GMT

Hello,

Count({<FACT={'A'}, FLAG={'1'}, ORDERRDATE={'>=$(vFromDate)<=$(vToDate)'}>}ORDER)

Count(total <Category>{<FACT={'A'},FLAG{'1'},ORDERDATE={'>=$(vFromDate)<=$(vToDate)'>}ORDER)

Try this. If still not working send me the app.

Serhan

Re: Pivot Table Issue

Tue, 21 Jul 2015 12:59:10 GMT

Its still not working. And I have shared the sample daat above, can't share the app.

Thanks

Re: Pivot Table Issue

qlikviewwizard — Tue, 21 Jul 2015 13:04:33 GMT

Hi ruchika123

Please post the some sample data for better understanding and exact solution.