# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

## Need help to resolve this problem

 SHOPID CheckOut EventTime CHECKOUT with Max_EventTime Count_CHK1 Count_CHK2 1 CHK1 5 1 CHK2 10 CHK2 1 2 CHK1 30 CHK1 1 2 CHK2 5 2 CHK2 10 3 CHK1 7 3 CHK2 9 CHK2 1 1 2 CHK1 CHK2

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
Highlighted
MVP

## Re: Need help to resolve this problem

Hi,

Try this as dimension.

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

Hope it works for you.

Regards,

Jagan.

8 Replies
Honored Contributor II

## Re: Need help to resolve this problem

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

## Re: Need help to resolve this problem

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?

MVP

## Re: Need help to resolve this problem

Hi,

Try this

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

Regards,

Jagan.

Not applicable

## Re: Need help to resolve this problem

Thanks Jagan, but it didn't work either.

Not applicable

## Re: Need help to resolve this problem

Attached a qvw with sample data.

Highlighted
MVP

## Re: Need help to resolve this problem

Hi,

Try this as dimension.

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

Hope it works for you.

Regards,

Jagan.

Not applicable

## Re: Need help to resolve this problem

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

Thank you so much.

Not applicable

## Re: Need help to resolve this problem

Dimension: SHOPID

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