topic Re: Set Analysis - Association between two table creating issues in QlikView

Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 15:07:49 GMT

Hi All,

I am new to Qlikview.

I am using below statement for Set Analysis.

=sum({ <[Report Month]={"<$(=[Report Month])"} > }[Review]

I have below two table which are Associated with field "Report Month".

Table1

ReportMonth Product Review

Jan-2014 ABC 1

Feb-2014 ABC 0

Mar-2014 ABC 1

Table2

ReportMonth ProductCatogory

Jan-2014 Consumer

Feb-2014 Consumer

Mar-2014 Consumer

Apr-2014 Consumer

here, ReportMonth is common in both table

and also we need to associate this two table for some other reason.

but, here, problem is that,

we are using field Reportmonth in set analysis which is associated from two table,

so, i believed,

we are getting some duplicated records.

is there any way in set analysis,

to explicitly considered ReportMonth from Table1 Only ?

any help is appreciated.

Thanks!.

Re: Set Analysis - Association between two table creating issues

MK_QSL — Mon, 28 Jul 2014 15:09:25 GMT

SUM({<[Report Month] = {'<$(=Max([Report Month]))'}>}Review)

Re: Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 15:13:22 GMT

Hi Hailey,

I would simply create a unique month field in table1 only and use that in your set analysis.

When you load your table 1, just create a new field

[Report Month] As Table1_Report_Month

Then use the new field in your set analysis, you are sure to get just results from table1

Edit: as Manish points out though you will need to adjust your modifier as you can't use =[Report Month] in that way

hope that helps

Joe

Re: Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 15:42:00 GMT

Joe,

i just need to replace Table1_Report_Month in below, correct ?

SUM({<[Report Month] = {'<$(=Max([Report Month]))'}>}Review)

Re: Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 15:57:44 GMT

Yea that should work , the set is effectively then selecting from the field from table1 then

Re: Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 16:58:19 GMT

Thanks.

I have tried this way,

but, still, it's not working.

duplicate records goes away,

but, we need SUM from all previous month,

that is not showing correctly.

I have Report Month is in the selection list box.

any suggestions ?

Re: Set Analysis - Association between two table creating issues

JonnyPoole — Mon, 28 Jul 2014 17:56:43 GMT

Can you share an example ? It doesn't sound too hard to solve but having the data and/or the qvw could help.

Re: Set Analysis - Association between two table creating issues

Mon, 28 Jul 2014 20:49:27 GMT

Ah right so you are selecting in Report Month I see, try the below maybe then or as Jonathan says post up some example to play with

SUM({<[Report Month] =, Table1_Report_Month={'<$(=Max([Report Month]))'}>}Review)

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:27:52 GMT

Hi ,

i have Attached sample file.

Thanks for your help.

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:34:38 GMT

Joe,

I have attached sample file for this issue in the main thread.

can you please look in that and let me know ?

Thanks!.

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:34:53 GMT

Joe,

I have attached sample file for this issue in the main thread.

can you please look in that and let me know ?

Thanks!.

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:35:26 GMT

Manish,

I have attached sample file for this issue in the main thread.

can you please look in that and let me know ?

Thanks!.

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:38:01 GMT

Hi Hailey,

the set analysis in that example looks to be working to me, unless you are wanting to include the current selected month plus previous?

In that case your expression is missing an equals sign

=sum({<[Month] = {'<=$(=Max([Month]))'}>}[Review?])

Edit: It also doesn't have the two tables as described in your question, but hopefully the above helps

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:49:35 GMT

Thanks for your Reply Joe.

Actually,

below is my requirment.

Example :

Curretnly,

if we select Jul-2014 for C865B it shows all the records from previous month.

we want,

if we select Jul-2014 for C865B it should show current month record and

do Sum of review from all prior months.

make sense ?

Re: Set Analysis - Association between two table creating issues

MK_QSL — Wed, 30 Jul 2014 15:50:37 GMT

Can you provide the sample data also? need to check...

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:54:40 GMT

Manish, I did not get you.

I have explained criteria to Joe.

does that make sense ?

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 15:56:41 GMT

Hi Hailey,

The above expression will do what you want then I believe, just need "<=" rather than the "<"

Re: Set Analysis - Association between two table creating issues

Wed, 30 Jul 2014 17:05:45 GMT

Joe,

it's returning multiple records more than one ,

in example month - July 2014.

it should return one record for selected month.

Re: Set Analysis - Association between two table creating issues

Thu, 31 Jul 2014 07:25:01 GMT

That's because you have the month dimension in there, if you take that out it will give you all the months total if that is what you are after

Joe