Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anil529106
New Contributor II

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

qlikdashboardqliksense_cloudqsense_user

5 Replies
MVP
MVP

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

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!

anil529106
New Contributor II

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

Thanks for reply..

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

anil529106
New Contributor II

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

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

richbyard
New Contributor III

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

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.

anil529106
New Contributor II

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

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.