topic Ticket analysis in QlikView

Ticket analysis

Mon, 27 Feb 2017 18:56:40 GMT

Good afternoon,

I would like to know if you have how I create an analysis, where I will select a product in a filter and bring all a ranking with the products that are on the same ticket as it. Example I select the product shirt and want to know all the tickets that have the product shirt and the other items. Such as pants first. So you're showing that I bought a T-shirt and pants on the same ticket.

Thanks for the help right away.

Jonathan

Re: Ticket analysis

vishsaggi — Mon, 27 Feb 2017 19:32:35 GMT

Can You provide some sample data and expected output you are looking for, for further help?

Re: Ticket analysis

Anonymous — Mon, 27 Feb 2017 19:33:00 GMT

=if(Aggr(Sum(1), Product)>0,

'Selected: $(=Concat(DISTINCT Product, ', '))',

'Others: $(=Concat({<Country=E({$})>} DISTINCT Product, ', '))'

)

Re: Ticket analysis

Mon, 27 Feb 2017 19:39:32 GMT

When selecting product t-shirt.

The result shows me that I have 10 tickets that have a shirt and pants and just below I have 5 tickets that have a shirt and gloves. That's what I need to present.

Re: Ticket analysis

jcs_2015 — Mon, 27 Feb 2017 21:02:48 GMT

Jonathan,

This sound familiar to a Cohort Analysis post I had published recently. I wanted to track a member population across time contingent on a set expression that defined their inclusion. I ended up creating a variable that concatenated a list of Member Ids (in your case it would likely be Order Ids) that can then be used in future set expressions. That variable that captured those Member IDs looked like this:

Concat(

MEMBER_ID = {'*'},

[Program Description] = ,

[Risk Level] = ,

[Report Date] = {"$(='>=' & Date($(vCPStart),'YYYY-MM')) $(='<=' & Date($(vCPEnd),'YYYY-MM'))"}

DISTINCT Chr(34) & MEMBER_ID & Chr(34),',')

Referencing that variable in future set expressions looked like this:

MEMBER_ID = {$(=$(vCohortMbr))}

That whole post is here with a sample App:

Help with Concatenated value variable in Set Expression

Re: Ticket analysis

Anonymous — Tue, 28 Feb 2017 15:35:26 GMT

count ({product={"T-SHIRT","pants"} }ticket)

count ({product={"T-SHIRT","gloves"} }ticket)

Re: Ticket analysis

Tue, 28 Feb 2017 17:45:35 GMT

Good afternoon, Jim.

Can do the calculation, but only with 1 day. When I put in a month it raises machine processing and memory consumption. For I have thousands of coupons for him to calculate. Do you know what might be happening?

Jonathan

Re: Ticket analysis

jcs_2015 — Tue, 28 Feb 2017 18:35:09 GMT

Your table is just likely too deep to be efficiently processed with every click in the UI.

How many variations of Product are there likely to be?

It might help performance to add some Flagging Dimensions via your ETL to move the resource demands to the load process versus dynamically via the UI.

Can you share a sample App? Might help all those on the forum to come up with a better solution.

Re: Ticket analysis

Tue, 28 Feb 2017 19:16:50 GMT

Jim,

I can have a variation with more than 2 thousand products. When you select the product code 70 it will need to show me all the tickets that contain the 70 and other products creating a ranking. For example I have the code 70 and the code 85006 in the first place that means that you buy a product and it also supplies gasoline.

Re: Ticket analysis

jcs_2015 — Tue, 28 Feb 2017 22:51:56 GMT

Sorry, I don't see how my solution would work "well" with that kind of complexity/size.