Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
|
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.
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?
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.
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
Thanks Marcus for your reply.
I've attached the data source file for you.
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)
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)