Crosstab and transpose function from Excel

[#1]

Hello Gurus,

Need some help here. I have a source file in the format that is not so straight forward. Have been trying out the cross tab and transpose function and it can work to a certain extend however i want to add additionally calculated fields.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8637.Variables_2D00_1.txt:550:0]

I've attached the sample source file. I want to add a few calculated metrics such as below:

set vGrossProfit = '(Sales - [Cost of Sales])';
set vOperatingProfit = '$(vGrossProfit)-[Operating expenses]';

The problem only occur when there is an additional dimension (Company) being added into the source file. Initially it was ok when there is just Item, and the Date.

Can anyone help?

[amars · Accepted solution]

Hi Johny,

See if the attached example helps you.

Thanks..

If this answers your question Please select Mark as an Answer

[#3]

Thanks Amar! This should be it. Guess we have to create multiple temp tables for it. Great

[#4]

Hi,

See if this works . if we use it in Exprs.. 🙂

Srini

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8231.Cross-Tbale.rar:550:0]