Announcements
cancel
Showing results for
Did you mean:
Contributor III

## How can I add different calculations to each cell of a table.

How can I add different calculations to each cell of a table.

in below table calculations for p1q1v,p1q2v,p2q1v,p2q2v, p3q1v,p3q2v are different.

Product  Quarter1 Quarter2
p1p1q1vp1q2v
p2p2q1vp2q2v
p3p3q1vp3q2v

5 Replies

If I understand your requirements correctly, the calculations are different for different products, right?

In this case, you should build your Measure formula as a conditional structure, using either a set of nested IF() statements or a combination of functions Pick() and Match(). For example:

Quarter 1 and Quarter 2 are two Measures. Each Measure should look like this:

IF(   Product = 'p1', <calculation for p1>,

IF(   Product = 'p2', <calculation for p2>,

...

))))

Keep in mind that this calculation will be a lot heavier than usual because Qlik will have to calculate all possible options and then pick one to present to you. So, If your formula should include 5 different calculations, it will take 5 times longer to calculate. Not a problem with a small data set, but can be a huge problem with a large data set.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Contributor III
Author

But here p1, p2,p3, quarter1 and quarter2 , all I have to put manually...they are not coming from database...

Contributor III
Author

Its resolved.

First created two inline tables for row (p1,p2,p3) and columns (quarter1,quarter2).

In pivot table used product inline table field for product in row and used quarter inline table for quarter in column.

Then used measure exp as below,

If (product=p1 and quarter=quarter1, p1q1v,

If(product=p1 and quarter=quarter2,p1q2v))...

Contributor III

Hi Anil,

Seems like an obvious answer but why would a pivot table not suffice in your example? The value you are calculating would appear to be a sum() or similar based on a split of the data for products 1,2,3 in quarters 1,2,3. In which case a pivot table with two dimensions would answer your question.

Contributor III
Author

Hi Richard,

Here all the cells including column names and row names changes as per data filter.

I have to write if else statement for all, for cells,for column names and row names.