Set Analysis FieldA = FieldB

[cbushey1]

Hi there,

I am looking to do set analysis where two different date fields have the same value.

Essentially, I have a data model where I have two calendars, A, B. What I need to display in my chart is the sum of some fields from Table B and the Sum of fields from Table A, all having the dimensions of Table A.

Table A:                    TableB:

CasesCreated          ProjectedAmount

CreateDate               BudgetDate

The set expression I am trying to build is

Sum({<CreateDate = BudgetDate>} ProjectedAmount)

Ideally, this expression would return the total Projected amount where the dates are equal.

Any thoughts?

I have already tried using the above and with p() to no avail.

Thanks!

[sunny_talwar]

May be use if statement:

Sum(If(CreateDate = BudgetDate, ProjectedAmount))

[Oleg_Troyansky]

With small amounts of data, the IF() solution might work. However, in a large application the performance will be extremely slow. Especially if the two tables are not associated - you are going to cause a Cartesian Join between the two tables.

Why not join the two tables by Date, or why not concatenate the two tables into a Concatenated Fact structure?

cheers,

Oleg Troyansky

Learn advanced Qlik techniques in my book QlikView Your Business.

[MK_QSL]

SUM({<CreateDate = {"=CreateDate = BudgetDate"}>}ProjectedAmount)

[cbushey1]

Ideally I would join them but given the data model I just can't do that. One date is coming from a master calendar built from many dates, canonical style as HIC would describe it. This new date comes from a newly introduced dataset that just doesnt flow with the other dates.This new table is at a very high level and it doesnt make sense to combine it with the more detailed data.

[cbushey1]

I tried this approach as well and it didnt return any results.

[MK_QSL]

Can you provide few lines of sample data ?

[cbushey1]

Here is a sample.

I didnt build out all my expression etc but you should get the idea.

[sunny_talwar]

I personally think that you need to use a canonical calendar here:

Canonical Date

[cbushey1]

Hi Sunny,

Thanks. I thought that too, and keep coming back to that idea but I dont think it is feasible with our data.

We have a MasterCalendar that is build from an Events table. This Events table is basically the Canonical table that HIC mentions. It has several dates coming to it from across the data model each being populated into a single field (EventDate) and being tagged with an EventName to represent that date. This works for all those data points because they can all be linked by an ActivityId. The problem is, my accountsummary data is at the account level and may not have any activities associated with it. So it doesnt really belong in this Events table (as no event may have occurred).