topic Re: Complementary Product Analysis in QlikView

Complementary Product Analysis

Fri, 31 Jul 2015 05:10:06 GMT

Hi,

All,

I am facing a problem.

I have a Dimension="SECTION"

another dimension="SECTION_Comp"(Which is created as the renaming of that dimension "SECTION" the resident from the same table where Section is......

I wanted to dispaly the absolute value of the billcount of the "SECTION" where the customer buys other than that section product in a pivot table.

i-e-If a section is "Boys" I wanted to display the billcount in which bills other than "Boys" means "Girls","Mens" and other divisions occur.

Now I have created an expression=

=if(SECTION=SECTION_Comp,0,count({<SECTION=p(SECTION_Comp), [BILLNO]=p([BILLNO])>}{<@_Date={">=$(vMonthStartCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}DISTINCT [BILLNO]))

It worked fine.

Now the additional requirement is that I want to display the percentage of the two

=(billcount of the sections where other than "Boys" section occurs

billcount of the sections where only "Boys" section occurs)*100%

Thanks in advance....

Re: Complementary Product Analysis

swuehl — Sat, 01 Aug 2015 10:36:44 GMT

It's unclear in which context you are evaluating these expressions. Since set analysis is evaluated once per chart, not per dimension line, I assume you are using this expression in a text box (or in a chart, but with dimensions SECTION and SECTION_Comp limited to single possible values).

A small sample application together with the expected result might help us to understand what you are trying to do.

Just guessing around, maybe something like this could work:

= (

if(SECTION=SECTION_Comp,0,count({<SECTION=p(SECTION_Comp), [BILLNO]=p([BILLNO])>}{<@_Date=

{">=$(vMonthStartCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}DISTINCT [BILLNO]))

)

count({<@_Date={">=$(vMonthStartCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}DISTINCT [BILLNO])

)

= (

if(SECTION=SECTION_Comp,0,count({<SECTION=p(SECTION_Comp), [BILLNO]=p([BILLNO])>}{<@_Date=

{">=$(vMonthStartCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}DISTINCT [BILLNO]))

)

count( TOTAL<SECTION>

{<@_Date={">=$(vMonthStartCY)<=$(vMonthEndCY)"},@_Month=,@_FinQuarter=,@_FinFullYear=>}DISTINCT [BILLNO])

)

But too much guessing...

Re: Complementary Product Analysis

Wed, 05 Aug 2015 05:45:51 GMT

Hi,

I am sending you demo qlikview app with excel data .

Please check the QV App,data and give me the solution.

I am facing to write the expression.

Regards,

Sukamal

Re: Complementary Product Analysis

swuehl — Wed, 05 Aug 2015 16:46:11 GMT

Not sure if this is the best data model, but it should work using

=Sum([Sheet1-1.MTD])

Sum( TOTAL<[Sheet1-1.Division_Grp]> if([Sheet1-1.Division]= [Sheet1-1.Division_Grp], [Sheet1-1.MTD]))