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?
This looks like something that could be solved using the aggr function. However it's difficult to give you the answer unless you attach a version of your application.
I agree with Marcus. A sample here would be very helpful.
try to share a sample qvw. By the time, try using a variable like:
vMaxStr= maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime)
=count({$<Transaction_Time={">$(vMaxStr)"}>}TransactionID)
I've tried this but this didn't work.
The problem I'm having is that when I put this expression against ShopNo it gives me the closing time fine but as soon as I put it in my set analysis expression the result is always zero:
Aggr(minstring({<DaysClose={"$(=WeekDay&' Close')"}>}ClosingTime)&':00',ShopNo)
But when I put the above expression in the main set analysis expression to get the closing time, it doesn't work:
=count({$<Transaction_Time={">$(=Aggr(maxstring({<DaysClose={'$(=WeekDay&' Close')'}>}ClosingTime)&':00',ShopNo))"}>}TransactionID)
I would add a flag in the script for "closing time" transaction id
I think the problem is the dynamic nature of closing time for each ShopNo.
The following expression works perfectly fine as long as I put it in straight table and against ShopNo as dimension.
maxstring({<DaysClose={"$(=WeekDay&' Close')"}>}ClosingTime)
But I'm failing to use the same expression within count expression below:
=count({$<Transaction_Time={">$(=maxstring({<DaysClose={'$(=WeekDay&' Close')"}>}ClosingTime))'}>}TransactionID)
now the above count expression has to get dynamic closing time for each shop and I think this dynamic nature of the above expression is failing.
Does anyone has any idea how to resolve this please?
Could you please elaborate a bit more on this?
Hi Everyone,
I've attached the application for your review. Looks like it's working but now how I want it to work.
The problem is, the second expression is picking up the maximum time for both shops. This needs to by dynamic. closing time for Tuesday 13 Jan 2015 is different for both shops so the no. of transactions should be 24 for shop 1 and 23 for shop 2 not 19 for shop 1.
Another problem is that I have to select a date to see the number of transactions. Can the expression aggregate all the transactions outside closing hours for all days?
Hi Marcus,
I've attached the application for your review. Looks like it's working but not how I want it to work.
The problem is, the second expression is picking up the maximum time for both shops. This needs to by dynamic. closing time for Tuesday 13 Jan 2015 is different for both shops so the no. of transactions should be 24 for shop 1 and 23 for shop 2 not 19 for shop 1.
Another problem is that I have to select a date to see the number of transactions. Can the expression aggregate all the transactions outside closing hours for all days?