Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Opposite of transpose

Hi there

I have a table shown below (which I created in a Table Box).

I want to show the same information in qlikview as follows (using a resident LOAD).

Could somebody help me with the transformation?

The reason why I want it like this is to create columns which show the percentage change in Cash amounts from one period to the next and then highlight those that exceed a certain percentage; using a straight table.

9 Replies

Re: Opposite of transpose

I would suggest creating a Pivot Table instead of making changes to your script.

Look here: The Generic Load

pooja_sn
Contributor

Re: Opposite of transpose

Add a Chart -> Pivot table.
Select 2 dimesions - 1. Emp_No

                                  2. Add a calculated dimension = 'P' &Period

Use after/before while calculating expression value accordingly to add comparisons.

Dimensions can be dragged and placed horizontally.

Not applicable

Re: Opposite of transpose

Ok, I've created the pivot but I've never used the before/after functions.  What do I do next from here to get the % change between periods.

Not applicable

Re: Opposite of transpose

ok, please ignore the above, I managed to get it.  Now I want to only show percentages > 50% in the pivot table.   How do I do this?

Below is just a screenshot of what I have so far.

pooja_sn
Contributor

Re: Opposite of transpose

Use below expression:

(sum(E_CashComp) - Before(sum(E_CashComp)))/Before(sum(E_CashComp))

To display the calculated values in percentage - Goto Properties -> Number tab -> Select expression and check 'Show in Percentage %'

Re: Opposite of transpose

Would you be able to share a sample of what you are seeing and what you intend to see?

Not applicable

Re: Opposite of transpose

The pivot table currently shows results for ALL employees, see below:

But I only what to see just the employees where the values exceed 50% such as employee number 00012048 where the increase from August to September was 67%.

Not applicable

Re: Opposite of transpose

Thanks Pooja

I managed to get this to work but I still cant figure out how to only show records (i.e. employees) where values are 50% and above. At the moment all records show, which I don't need.   See previous post.

Not applicable

Re: Opposite of transpose

Hi Hetal,

I think you can write using if loop like below. And you can omit the values which are null by using the property supress null values.

if(% value >50 , fine,null)

Thanks,
Sreeman.