Multiple Resolutions displaying only the primary

[hkcuhC]

I have enclosed my SQL to see if anyone can offer a suggestion on how to accomplish my task. If you look at the creation of temporary table ZZMD07, the first on is what is being generated and the second on is what I would like it to be.

What I am attempting to do is find out what stores customers purchase in and rank them based on the amount of spending. Once I rank the customers stores, give me the sales in the number one store and give me the sales in all the other stores.

Where I am having trouble is on the report resolution. Trying to show the sales for the number one store (primary store) along with the other store sales on the same row. So that the report would look kind of like:

________________________________________________________________________________
Store# Str Sales Other Store Sales
Store number 001 $48,000.00 $10,000

Hopefully, you can help? Thank you for all suggestions.


/* This step ranks a customer’s spending store based on sales within that store */
create global temporary table ZZAM00
on commit preserve rows as
select a13.HH_ADDR_KEY HH_ADDR_KEY,
a11.LOC_KEY LOC_KEY,
rank () over(partition by a13.HH_ADDR_KEY order by sum(a11.NET_SALES_AMT) asc) WJXBFS1
from F_LC_STR_TRANS_WK_VW a11,
D_TF_WEEK_VW a12,
D_CARD_VW a13,
D_STORE_VW a14
where a11.TIMEFRAME_KEY = a12.WK_KEY and
a11.LYLTY_CARD_KEY = a13.LYLTY_CARD_KEY and
a11.LOC_KEY = a14.LOC_KEY
and (a14.REGION_DESC in ('EASTERN')
and a12.PRD_KEY in (10792)
and a11.LYLTY_CARD_KEY > 0)
group by a13.HH_ADDR_KEY,
a11.LOC_KEY


/* This step returns only a customer’s number 1 spending store */
create global temporary table ZZMQ01
on commit preserve rows as
select pa1.HH_ADDR_KEY HH_ADDR_KEY,
pa1.LOC_KEY LOC_KEY
from ZZAM00 pa1
where (pa1.WJXBFS1 = 1)


/* This step builds the TEMP table for metric collection */
create global temporary table ZZRF02
on commit preserve rows as
select distinct pa2.HH_ADDR_KEY HH_ADDR_KEY,
pa2.LOC_KEY LOC_KEY
from ZZMQ01 pa2,
D_CARD_VW r11,
D_TF_PRD_VW r12,
D_STORE_VW r13
where pa2.HH_ADDR_KEY = r11.HH_ADDR_KEY and
pa2.LOC_KEY = r13.LOC_KEY
and (r13.REGION_DESC in ('EASTERN')
and r12.PRD_KEY in (10792)
and r11.LYLTY_CARD_KEY > 0)


/* This step ranks a customer’s spending store based on sales within that store */
create global temporary table ZZAM03
on commit preserve rows as
select a13.HH_ADDR_KEY HH_ADDR_KEY,
a11.LOC_KEY LOC_KEY,
rank () over(partition by a13.HH_ADDR_KEY order by sum(a11.NET_SALES_AMT) asc) WJXBFS1
from F_LC_STR_TRANS_WK_VW a11,
D_TF_WEEK_VW a12,
D_CARD_VW a13,
D_STORE_VW a14
where a11.TIMEFRAME_KEY = a12.WK_KEY and
a11.LYLTY_CARD_KEY = a13.LYLTY_CARD_KEY and
a11.LOC_KEY = a14.LOC_KEY
and (a14.REGION_DESC in ('EASTERN')
and a12.PRD_KEY in (10792)
and a11.LYLTY_CARD_KEY > 0)
group by a13.HH_ADDR_KEY,
a11.LOC_KEY


/* This step returns all of a customer’s spending stores except the number 1 ranked store */
create global temporary table ZZMQ04
on commit preserve rows as
select pa4.HH_ADDR_KEY HH_ADDR_KEY,
pa4.LOC_KEY LOC_KEY
from ZZAM03 pa4
where (pa4.WJXBFS1 > 1)


/* This step builds the TEMP table for metric collection */
create global temporary table ZZRF05
on commit preserve rows as
select distinct pa5.HH_ADDR_KEY HH_ADDR_KEY
from ZZMQ04 pa5,
D_CARD_VW r11,
D_STORE_VW r12,
D_TF_PRD_VW r13
where pa5.HH_ADDR_KEY = r11.HH_ADDR_KEY and
pa5.LOC_KEY = r12.LOC_KEY
and (r12.REGION_DESC in ('EASTERN')
and r13.PRD_KEY in (10792)
and r11.LYLTY_CARD_KEY > 0)


/* This step collects the net sales for the customer’s number 1 store */
create global temporary table ZZMD06
on commit preserve rows as
select a11.LOC_KEY LOC_KEY,
sum(a11.NET_SALES_AMT) BASKETNETSAL
from F_LC_STR_TRANS_WK_VW a11,
D_CARD_VW a12,
ZZRF02 pa3
where a11.LYLTY_CARD_KEY = a12.LYLTY_CARD_KEY and
a11.LOC_KEY = pa3.LOC_KEY and
a12.HH_ADDR_KEY = pa3.HH_ADDR_KEY
group by a11.LOC_KEY


/* This step collects the net sales for all the other customer’s store */
/* This is what is generated */
create global temporary table ZZMD07
on commit preserve rows as
select a11.LOC_KEY LOC_KEY,
sum(a11.NET_SALES_AMT) BASKETNETSAL
from F_LC_STR_TRANS_WK_VW a11,
D_CARD_VW a12,
ZZRF05 pa6
where a11.LYLTY_CARD_KEY = a12.LYLTY_CARD_KEY and
a12.HH_ADDR_KEY = pa6.HH_ADDR_KEY
group by a11.LOC_KEY

/* This is what I would like generated */
create global temporary table ZZMD07
on commit preserve rows as
select pa7.LOC_KEY LOC_KEY,
sum(a11.NET_SALES_AMT) BASKETNETSAL
from F_LC_STR_TRANS_WK_VW a11,
D_CARD_VW a12,
ZZRF05 pa6,
ZZRF02 pa7
where a11.LYLTY_CARD_KEY = a12.LYLTY_CARD_KEY and
a12.HH_ADDR_KEY = pa6.HH_ADDR_KEY and
pa6.HH_ADDR_KEY = pa7.HH_ADDR_KEY
group by pa7.LOC_KEY


/* This step builds the report */
select pa7.LOC_KEY LOC_KEY,
a11.LOC_DESC LOC_DESC,
pa7.BASKETNETSAL BASKETNETSAL,
pa8.BASKETNETSAL BASKETNETSAL1
from ZZMD06 pa7,
ZZMD07 pa8,
D_STORE_VW a11
where pa7.LOC_KEY = pa8.LOC_KEY and
pa7.LOC_KEY = a11.LOC_KEY

 

[IFK]

How about you try to create one metric that sums up the sales all of the stores, eg. named All Stores Sales.

For that you create a metric on the sales fact and set the level to: target = store with an absolute filter.

Then create a second metric called Other Stores Sales that does the following calculation:
[All Store Sales] - [Str Sales]

Put Other Stores Sales in your report.

I think this should give the required result.

 

[nlim]

just a point of clarification, does the report look like this?

store str sales other store sales
----- --------- -----------------
store #1 48,000 10,000
store x 2,000 10,000
store y 3,000 10,000
store z 5,000 10,000

note the sales for all other stores add up to 10,000 but the "other store sales column" remains the same...

 

[hkcuhC]

IFK, thank you for your assistance.

I appreciate the input. It works fine.

My report needs to go one step further:
1. I needed to get [All Store Sales] that you outlined and
this works fine.
2. I needed to get [All Customer Sales] regardless of
location / store. This is not a problem.
3. I needed to get a customer's "number one ranked"
location / store (primary store) that a customer
spent the most in, using the ranking function. This
is not a problem.
4. What I am having a problem accomplishing is:
Merge steps #2 and #3 together. Using the 'primary
store', arrived at in step #3, with the total sales,
arrived at in step #2. Merging the two temp tables by
using the customer Id. Display only the 'Primary
Store' and the summarized sales.

Is this do-able? If so, how would you accomplish it.
Again, thank you for all your input.

 

[hkcuhC]

Nlim, the way the report would look as outlined by IFK is:

Primary
All Store Store Other Store
Store Sales Sales Sales
_____ __________ _______ _____________

4006 $47,122.98 $18,681.79 $28,441.19
4010 $44,170.13 $15,782.49 $28,387.64
... ... ... ...
... ... ... ...

The 'Other Store Sales' would be a calulation between
All Sales and my primary store customer sales.

 

[IFK]

Ok, that was a misunderstanding.

I guess you need to select one customer. Then you just want to show his no. 1 ranked store, the sales of the customer in this store and the sales of the customer in all other stores, right?

First you need to build a metric that adds up all the customer's sales and ignores the stores:

All Customer Sale
Formula sum(salesFact)
target = customer filter= absolute grouping=standard
target = store filter= ignore grouping=none

Then you need to build a metric that substracts the regular sales metric from All Customer Sale

Other Stores Sales
[All customer Sales] - [Sales]

I suppose you have a simple sales metric.

Than you drag store, sales and Other Stores Sales on the template and a customer in the report filter.

That would deliver the following report for a customer with sales of $5,000

Store Sales Other Store
Sales
001 $500 $4,500
002 $2,500 $2,500
003 $1,000 $4,000
004 $1,000 $4,000

But you want to display only line 2. For that you add a report filter that qualifies on the sales metric:

Evaluate the set at default Metric level Where(Rank of 'sales' Top 1).

If you execute the report now it shows only:

Store Sales Other Store
Sales
002 $2,500 $2,500

I hope that was the right answer now.

 

[nlim]

hkcuhC, I looked thru the SQL generated and what you said you wanted. Here's my understanding. I'll propose a solution if my description below is accurate.

For store S1 you want to find out which customers spent the most money there. Let's called these customers SC1. Now you want to know how much SC1 spent at S1, and also how much SC1 spent at all other stores besides S1. This is on one row of the query. Each row thus represents another store S2...S3. So unless a customer spent the same top amount in 2 stores, each customer's sales should only show up in one particular row.

I think this is what your desired SQL will give you. If this is right, then my proposed solution is a little different from your approach so far....here goes:

1) create a view in your db called "all_other_stores_vw" defined as
select a1.loc_key, a2.loc_key ALL_OTHER_LOC_KEY
from D_STORE_VW a1, D_STORE_VW a2
where a2.loc_key <> a1.loc_key
2) add this view to your project and update schema.
3) create a transformation call StoreTransform in your project. It should be keyed off the store attribute. Make it many-to-many. choose the ALL_OTHER_STORES_VW table, and pick ALL_OTHER_LOC_KEY column as the expression.
4) now make a copy of your primary store sales metric M1. Add the ProductTransform to M1.
5) now use only primary store sales and M1 in your report.

The transform logic is pretty straightforward and similar to date transforms. For one date, you want previous year sales. For one store, you want other store sales.

let us know if it works.