Fixed Column Analysis

[#1]

Hi,

I've got a problem which is easy to solve in Excel but is proving intractable in QlikView. I have a chart as such:

     2015-01-13                                   2015-02-12                                   2015-03-11

A          B          C                         A          B          C                            A          B          C

9          2           2.25                     8          7          2                            12          4          3

7          3           1.16                     6          4          1                            15          6          2.5

8          4            1.6                      7          5         1.4                          14          5          2.8

Now, consider the following:

1. Column C (Green) is a calculated column. C = A/B
2. Also notice that Column C is calculated using Column A (Blue) in each set but only Column B (Red) from the last set
   (2015-03-11). This is why the Column Bs in the first two sets (2015-01-13 & 2015-02-12) are left gray.

How can I do this in QlikView? In Excel it's a simple task of "locking" one column and dynamically calculating from the others. In QlikView this does not seem possible.

Any help is much appreciated.

-Abhijeet.

[sunny_talwar]

Probably something like this:

Sum(A)/Sum({<Date = {'$(=Min(Date))'}>}B)

Best,

S

[#3]

Thanks but I've already tried that with no luck. This approach only gives me a value for Column C in the set with the Min(Date), and Column C in the other sets (other dates) show up blank.

The Set analysis here seems to interpret that it should take A and divide by B, but only if the Date above B is min(Date) (or max as the case may be in this example).

Set analysis doesn't seem to work here. Neither do alternate states.

Ab

[sunny_talwar]

What if we add a 1 to the equation here. Can you try this?

Sum(A)/Sum({1<Date = {'$(=Min(Date))'}>}B)

Best,

S

[sunny_talwar]

Would you be able to share a sample file where I can play around with a little.

Best,
S

[#6]

Attached to original post. Thanks again for your help!

Ab.

[ramoncova06 · Accepted solution]

I prefer to create a variable for whatever date you need and then add that into the set analysis

SUM(A)/Sum(TOTAL <Customer> {<Date = {'$(=vMaxDate)'}>}B) !

[#8]

This appears to work. Thank you!

Ab.

[#9]

Hi,

Try like this in Straight table.