Set Analysis in Pivot Chart

[#1]

Hello all.  I'm hoping for some advice on the set analysis that I'm trying to do in a pivot chart.

I have a straight table chart that provides some financial calculations of order that were active on a given date.  Looking at this historically if the client is currently in a status of terminated, but wasn't as of the period end date that is being reviewed then that order should be included in the list.

Sum({1<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Current'}>+<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Former'},ContractTermination={">$(=Date(PeriodDate))"}>}((term_with_disc * CurrentExchRate)/12) * term)

This is working very nicely when the user is selecting the period date that they need to review.

Now they require the ability to show summary data by year and month where the year sub total line will actually be the monthly average.

I took a copy of my straight table chart and converted it to a pivot table chart and switched out the dimensions so that there is Year(PeriodDate) and Date(PeriodDate,'MMM-yy')

The expression above became:

Avg(Aggr(Sum({1<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Current'}>+1<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Former'},ContractTermination={">$(=Date(P(PeriodDate)))"}>}((term_with_disc * CurrentExchRate)/12) * term),PeriodDate))

On the surface it seemed to be working properly but I discovered that there is some issue with the second part of the statement where I'm bringing in the data for the 'Former' clients based on their termination date.  If I select a period end value it will be correct for that period, however that means that I don't get the rest.  Essentially without a PeriodDate value selected that part of the expression seems to always be true, therefore I'm getting a much higher number than I should.

I've tried the following variations for the date section of the set analysis expression to no avail:

ContractTermination={">$(=Date(PeriodDate))"}

ContractTermination={"$(=Date(ContractTermination)>$(=Date(PeriodDate))"}

ContractTermination={"$(=Date(ContractTermination)>$(=Date(P(PeriodDate)))"}

and a few others but I've lost track.

Basically I can't see why the set analysis won't using the actual period date associated with the records being evaluated.  Contract Termination and Period Date are both fields on the record.

Thanks,

Lorna

[fosuzuki · Accepted solution]

Hi Lorna,

I'd suggest you to include in your fact table a flag that indicates for each transaction if the client is active or not at the transaction date. So, for a Client with ContractTermination = 12/31/2012, your Fact table would be (As I don't know your data model, this is just a sample):

TransId, TransDate, ClientId, FlagActiveClient

1, 1/1/2012, 123, 1

2, 12/1/2012, 123, 1

3, 12/31/2012, 123, 1

4, 1/1/2013, 123, 0

5 2/1/2013, 123, 0

And then in your set analysis you can use something like:

Avg(Aggr(Sum({1<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Current'}>+1<TransType={4,5,6,7},PeriodDate=P(PeriodDate),Client_Status__c={'Former'},FlagActiveClient={1}>}((term_with_disc * CurrentExchRate)/12) * term),PeriodDate))

The problem with your set analysis is that when you use PeriodDate as Dimension, you can't dynamically use each dimension value in your expression. The easiest explanation is that QlikView will first (just once) evaluate the set analysis of your expression to generate the "data cloud" for the calculations, and then use it to do the math and aggregate by your dimension(s). So, when QV is internally generating the "data cloud", it doesn't yet know what are the dimension values... Hope I could explain it well...

Regards

Fernando

[#3]

Thank you Fernando.

I appreciate the explanation of when the set analysis is evaluated.  That's going to help me a lot going forward.

Lorna