topic Re: Need help to resolve this problem in QlikView

Need help to resolve this problem

Thu, 28 Jun 2012 06:22:05 GMT

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.

Re: Need help to resolve this problem

Jason_Michaelides — Thu, 28 Jun 2012 06:31:31 GMT

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

Re: Need help to resolve this problem

Thu, 28 Jun 2012 11:43:12 GMT

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?

Re: Need help to resolve this problem

jagan — Thu, 28 Jun 2012 13:09:13 GMT

Hi,

Try this

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

Regards,

Jagan.

Re: Need help to resolve this problem

Thu, 28 Jun 2012 13:27:30 GMT

Thanks Jagan, but it didn't work either.

Re: Need help to resolve this problem

Thu, 28 Jun 2012 23:43:49 GMT

Attached a qvw with sample data.

Re: Need help to resolve this problem

jagan — Fri, 29 Jun 2012 05:57:31 GMT

Hi,

Try this as dimension.

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

Hope it works for you.

Regards,

Jagan.

Re: Need help to resolve this problem

Fri, 29 Jun 2012 06:00:46 GMT

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

Thank you so much.

Re: Need help to resolve this problem

Fri, 29 Jun 2012 06:07:25 GMT

Dimension: SHOPID

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