Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis challenge

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

14 Replies
marcus_malinow
Partner - Specialist III
Partner - Specialist III

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.

sunny_talwar

I agree with Marcus. A sample here would be very helpful.

tresesco
MVP
MVP

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)

Anonymous
Not applicable
Author

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)

maxgro
MVP
MVP

I would add a flag in the script for "closing time" transaction id

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

Could you please elaborate a bit more on this?

Anonymous
Not applicable
Author

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?

Anonymous
Not applicable
Author

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?