topic how to create intersecting sets ni dimension in QlikView

how to create intersecting sets ni dimension

Wed, 18 Aug 2010 12:30:27 GMT

Hi All ,

I am finding out solution for below mentioned problem , hope somebody can help me out :

I have secenario where i need to create one dimension to indicate nature of buyer.

"Nature of buyer" categories are :

1) Regular : Who is purcahsing througout the year .

2) Special : Who is purcahsing "only in festive periods" ( i.e. Jan, Oct ,Dec of every Year )

3) ExtraSpecial : Who is purcahsing "only during promotions" ( i.e. 15th Jan - 15th Feb & 15 Jul - 15 th Aug of every Year )

Now if you look into above categories "Spcial" & "Extraspecial" are paritally overlapping with each other , but still its OK to show member in both brackets if condition matches .

My requirement is to show in table chart "Mbr count" against these categories & on selecting particular category i must get member details (which will be basic contact info abt members).

I am really worried abt getting parital intersction sets in sigle dimension ?

Looking for best possible answer ( example will be of great support)

Thanks,

Bhushan N

how to create intersecting sets ni dimension

Fri, 20 Aug 2010 23:49:28 GMT

Hi,

first you have to identify the buyers bought something within your timeframey

Therefore i would create a calendar for each Special/extraspecial day with a flag for Special and ExtraSpecial.

Then create all days within the calendar are not special/extraspecial with a regular flag.

Join that calendar to jour sells. so you will have one or more flags for each sell.

Load all sells group by byer and year and find out if he has flag (if you use a 1 as a flag you could use the aggregation MAX() ). Also keep the year.

Reload that table into an other table and calcuzlate your dimension (could also be done while aggregation)

IF(Regular=1, 'Regular', IF(Special=1, IF (Extraspecial=1,"Special & Extraspecial', 'Special'), IF(Extraspecial=1,'Extraspecial', 'No Buyer')) AS ByerDimension

(be aware, i mention you using the Selling Table, so the situation "No buyer" should not occur. To find out who has nothing bought you have to expand that logic)

Than create a additionally table out of that aggregiated table with a link to your byers. Because you have keeped the year you could also use that as a dimension.

Hope that helps, best regards

Michael

how to create intersecting sets ni dimension

johnw — Sat, 21 Aug 2010 01:50:05 GMT

Here's one possibility if I understood. I'm guessing you only want those three categories, and for some buyers to be in two categories. I'm guessing you don't actually want a "Special & Extraspecial" category.

BuyerCategories:
LOAD
Buyer
,subfield(if(BC2='ExtraSpecial',BC2&if(BC1='Special',':'&BC1),BC1),':') as BuyerCategory
;
LOAD
Buyer
,if(min(match(month(Date),'Jan','Oct','Dec')),'Special','Regular') as BC1
,if(min((month(Date)='Jan' and day(Date)>=15)
or (month(Date)='Feb' and day(Date)<=15)
or (month(Date)='Jul' and day(Date)>=15)
or (month(Date)='Aug' and day(Date)<=15)),'ExtraSpecial') as BC2
RESIDENT Sales
GROUP BY Buyer
;

The subfield() is what gives you an extra row for the Buyer when they're in two categories. Then just count(distinct Buyer) with a dimension of BuyerCategory. See attached.

how to create intersecting sets ni dimension

Mon, 30 Aug 2010 08:51:38 GMT

Thanks John for your inputs ,let me try using this approach in my doc

Regards,

Bhushan N