Set Analysis challenge

[Anonymous]

Hi

I have a table that has transactions for different shops and another table that contains the closing times for these shops. I need to count number of transactions occurred after closing time.

Below is the straight table I want

ShopNo	No. of transactions after closing time
1	58
2	52
3	88

Now the problem I am getting is in my set analysis expression where I am using maxstring function. This Maxstring function is ignoring the ShopNo dimension in my straight table. Below is my expression:

=count({$<Transaction_Time={">$(=maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime))"}>}TransactionID)

The expression works fine as long as I've selected a shop number but it gives me 0 if non of the shops are selected.

• Transaction_Time is the time of transaction
• DaysClose is coming from the table that contains the day name, shop no. and closing time.
• ClosingTime is the closing time of shop
• TransactionID is what I need to count
• WeekDay is the day Name

For your ease below is the table that contains closing time with shop no and days

ShopNo	DaysClose	ClosingTime
1	Mon Close	17:30:00
1	Tue Close	17:30:00
1	Wed Close	17:00:00

I've looked into the problem in detail and what I've found is that the maxstring function is ignoring the Shop No dimension of my straight table. is there anyway I can include the ShopNo in my Maxstring function? so I can see all the shops with transactions outside closing time?

[#2]

Hi,

Join two tables using ShopNo and DaysClose to get ClosingTime instead of ShopNo.

LOAD [Shop No],
  [Shop No]&DaysClose as Key,
     time(ClosingTime,'hh:mm:ss') as ClosingTime,
     DaysClose
FROM
[SetAnalysisChallenge.xls]
(biff, embedded labels, table is Timings$);
Transactions:
LOAD [Shop No]&WeekDay(TransactionDate) as Key,
     TransactionTime,
     TransactionDate,
     TransactionID,
     WeekDay(TransactionDate) as Week_Day
FROM
[SetAnalysisChallenge.xls]
(biff, embedded labels, table is Transactions$);

Use the following expression to get the count of transactions after closing time.

count({<TransactionTime={">$(=maxstring(total <[Shop No],TransactionDate> ClosingTime))"}>}TransactionID)

Hope it helps.

[marcus_malinow]

Ok,

looking at your data model I have a suggestion. It won't immediately solve this problem, but will get us one step closer.

I'd suggest instead of linking Timing and Calendar to Data, set up a link table. At the moment there's no link between DaysClose and WeekDay, and really there should be.

If you need help with this, upload your test data sources.

Marcus

[Anonymous]

Thanks Marcus for your reply.

I've attached the data source file for you.

[sasikanth]

HI,

Since both these fields Closing time and Transaction time are in same table,

Try to create a flag at script level

like

Load *,

if(Closing_time<Transaction_time,1,0) as Trans-Flag  FROM Table;

Handle if any formatting  issues

Use this in your table

=Count({<Trans_Flag={1}>}TransactionID)

[Kushal_Chawda]

You can handle it in script itself

Timing:

mapping LOAD ShopNo&DaysClose as key,

          time(ClosingTime,'hh:mm:ss') as ClosingTime

FROM

[ShopClosingTime.xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD *,

if(frac(Transaction_Time)>=Frac(ClosingTime),1,0) as Flag;

LOAD *,

ApplyMap('Timing',ShopNo&WeekDay,null()) as ClosingTime;

LOAD ShopNo,

    TransactionID,

    time(Transaction_Time,'hh:mm:ss') as Transaction_Time,

    TransactionDate,

    WeekDay(TransactionDate) as WeekDay

FROM

[ShopClosingTime.xlsx]

(ooxml, embedded labels, table is Transactions);

Now you can simply write the expression as

=count({<Flag={"1"}>}TransactionID)