topic Re: Fixed Column Analysis in QlikView

Fixed Column Analysis

Wed, 11 Mar 2015 20:27:39 GMT

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:

Column C (Green) is a calculated column. C = A/B
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.

Re: Fixed Column Analysis

sunny_talwar — Wed, 11 Mar 2015 20:31:59 GMT

Probably something like this:

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

Best,

Re: Fixed Column Analysis

Wed, 11 Mar 2015 20:40:44 GMT

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.

Re: Fixed Column Analysis

sunny_talwar — Wed, 11 Mar 2015 20:42:58 GMT

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

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

Best,

Re: Fixed Column Analysis

sunny_talwar — Wed, 11 Mar 2015 20:46:00 GMT

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

Best,
S

Re: Fixed Column Analysis

Wed, 11 Mar 2015 20:54:29 GMT

Attached to original post. Thanks again for your help!

Ab.

Re: Fixed Column Analysis

ramoncova06 — Wed, 11 Mar 2015 22:11:22 GMT

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

Re: Fixed Column Analysis

Wed, 11 Mar 2015 22:17:07 GMT

This appears to work. Thank you!

Ab.

Re: Fixed Column Analysis

Thu, 12 Mar 2015 10:05:03 GMT

Hi,

Try like this in Straight table.