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: 
Not applicable

Need help to resolve this problem

SHOPIDCheckOutEventTimeCHECKOUT with Max_EventTimeCount_CHK1Count_CHK2
1CHK15


1CHK210CHK2
1
2CHK130CHK11
2CHK25


2CHK210


3CHK17


3CHK29CHK2
1




12




CHK1CHK2

I came across a scenario as below and need your kind help to solve it in QlikView.

Scenario:

SHOPID is the number given to a 'Shopper' and the Checkout is the checkout number. EventTime is the time spent in each Checkout. Due to an error in location system, a shopper is seen in more than one checkout (i.e Shopper 1 in checkout 1&2 and shopper 2 in checkout 1 and twice in 2 etc.). The correct register is the one that shopper spent most of the time (i.e max EventTime per SHOPID).

Calculation:

Need to count the distinct SHOPID s per CheckOut and display it in a Bar Garph.

A spreadsheet is attached to make it easy to understand.

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this as dimension.

=aggr(FirstSortedValue(CheckOut, -EventTime),SHOPID)

Hope it works for you.

Regards,

Jagan.

View solution in original post

8 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi,

If CheckoutWithMaxTime always has at least one value for each SHOPID then you could just use this as the dimension and check the "hide null values" box. Simple Count(DISTINCT SHOPID) then.

Hope this helps,

Jason

Not applicable
Author

Thanks Jason, I tried the following expressionas in the dimension but it didn't work.

=if(EventTime=aggr(max(EventTime),SHOPID),CheckOut)

Where am I making the mistake?

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

=aggr(if(EventTime=max(EventTime), CheckOut),SHOPID)

Regards,

Jagan.

Not applicable
Author

Thanks Jagan, but it didn't work either.

Not applicable
Author

Attached a qvw with sample data.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this as dimension.

=aggr(FirstSortedValue(CheckOut, -EventTime),SHOPID)

Hope it works for you.

Regards,

Jagan.

Not applicable
Author

Hey Jagan, you're a genius, it worked.

Thank you so much.

Not applicable
Author

Dimension: SHOPID

Expression : =aggr(max(EventTime),SHOPID)