Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fixed Column Analysis

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.

1 Solution

Accepted Solutions
ramoncova06
Specialist III
Specialist III

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

View solution in original post

8 Replies
sunny_talwar

Probably something like this:

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

Best,

S

Not applicable
Author

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

Not applicable
Author

Attached to original post. Thanks again for your help!

Ab.

ramoncova06
Specialist III
Specialist III

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

Not applicable
Author

This appears to work. Thank you!

Ab.

Not applicable
Author

Hi,

Try like this in Straight table.

Untitled.png