Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
---|---|---|
p1 | p1q1v | p1q2v |
p2 | p2q1v | p2q2v |
p3 | p3q1v | p3q2v |
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!
Thanks for reply..
But here p1, p2,p3, quarter1 and quarter2 , all I have to put manually...they are not coming from database...
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))...
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.
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.