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