topic Re: Set Analysis FieldA = FieldB in QlikView

Set Analysis FieldA = FieldB

cbushey1 — Thu, 20 Oct 2016 15:48:21 GMT

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!

Re: Set Analysis FieldA = FieldB

sunny_talwar — Thu, 20 Oct 2016 15:49:23 GMT

May be use if statement:

Sum(If(CreateDate = BudgetDate, ProjectedAmount))

Re: Set Analysis FieldA = FieldB

Oleg_Troyansky — Thu, 20 Oct 2016 15:53:45 GMT

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.

Re: Set Analysis FieldA = FieldB

MK_QSL — Thu, 20 Oct 2016 16:27:45 GMT

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

Re: Set Analysis FieldA = FieldB

cbushey1 — Thu, 20 Oct 2016 16:36:21 GMT

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.

Re: Set Analysis FieldA = FieldB

cbushey1 — Thu, 20 Oct 2016 16:36:38 GMT

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

Re: Set Analysis FieldA = FieldB

MK_QSL — Thu, 20 Oct 2016 16:53:11 GMT

Can you provide few lines of sample data ?

Re: Set Analysis FieldA = FieldB

cbushey1 — Thu, 20 Oct 2016 17:20:08 GMT

Here is a sample.

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

Re: Set Analysis FieldA = FieldB

sunny_talwar — Thu, 20 Oct 2016 17:26:27 GMT

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

Canonical Date

Re: Set Analysis FieldA = FieldB

cbushey1 — Thu, 20 Oct 2016 17:36:52 GMT

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

Re: Set Analysis FieldA = FieldB

Anonymous — Fri, 21 Oct 2016 15:33:30 GMT

You can do an if statement with a calculated dimension and then you don't need set analysis in your expressions at all. Oleg is right though it would be a Cartesian join that may not perform very well if you have a lot of data.

But your calculated dimension would be something like:

if (CreateDate=BudgetDate,CreateDate,'')

Then you can create your expressions normally as sum(ProjectedAmount) or whatever it may be.