topic Re: Count/distinct over time in QlikView

Count/distinct over time

Wed, 01 Jul 2015 18:56:31 GMT

I am working on counting how many times the promise date for an order changed over time. The pivot table I made looks like this:\\\

Order	3/2/2015	3/3/2015	3/4/2015
2312323	5/3/2015	5/6/2015	5/10/2015
1234324	6/3/2015	6/3/2015	6/3/2015
5678901	4/16/2015	4/19/2015	4/16/2015

The dates in the data cells are the promise dates. My question is if there's a way of counting how many times each order's promise dates changed during the life of the order? Currently I am using Count(Distinct()) but in a case like Order #5678901, it would only count that it changed once because it went back to the same promise date.

This is my current expression in the table where I am counting: if((count(distinct([Promise Date]))-1)>=0,(count(distinct([Promise Date]))-1),'-')

The results I would want in this example would be:

Order, # of Changes

2312323, 2

1234324,0

5678901,2

I'm not sure if this is possible but I would appreciate any help I can get.

Re: Count/distinct over time

maxgro — Wed, 01 Jul 2015 19:12:55 GMT

maybe in the script

load * inline [

Order, Date, Premise

2312323, 3/2/2015, 5/3/2015

2312323, 3/3/2015, 5/6/2015

2312323, 3/4/2015, 5/10/2015

1234324, 3/2/2015, 6/3/2015,

1234324, 3/3/2015, 6/3/2015

1234324, 3/4/2015, 6/3/2015

5678901, 3/2/2015, 4/16/2015

5678901, 3/3/2015, 4/19/2015

5678901, 3/4/2015, 4/16/2015

];

load *, if(Order=Peek(Order) and Premise<>peek(Premise), 1, 0) as FlagChanged

Resident a

order by Order, Date;

drop Table a;

then the chart is just a sum(FlagChanged)

Re: Count/distinct over time

marcus_sommer — Wed, 01 Jul 2015 19:16:49 GMT

Maybe you could include another field to make promise date unique:

count(distinct [Promise Date]&[Another Field])

If not you will need some within the script, maybe a rowno() or calculate it directly within an ordered resident-load with peek() or previous(): Peek() vs Previous() – When to Use Each

- Marcus

Re: Count/distinct over time

Wed, 01 Jul 2015 19:17:30 GMT

I can't make an inline table like that in the script though, mainly because this data in my pivot table is being pulled from many files and has thousands of orders in each file. The table I created in this discussion pretty much just outlines what I am looking at for data. Sorry I didn't make that clear! So I'm not sure if I can do the rest of your suggestion.

Re: Count/distinct over time

marcus_sommer — Wed, 01 Jul 2015 19:24:07 GMT

The Inline from maxgro was also just an example. You could simply use your table-load - maybe you need to load them twice if your table isn't ordered properly for the peek()-function.

- Marcus

Re: Count/distinct over time

Wed, 01 Jul 2015 19:46:43 GMT

All of the files I am loading look more like:

[Aged Orders - March]:

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as Date,

//Above line is for pulling date from file name

[Sales Doc.],

Item,

Upper(Status) as Status,

[Promise Date],

[Sales Doc.] & Item as [Order],

Since the Date is created from an expression in the script, can you still use Peek and Premise like maxgro‌ suggested? I'm just confused on how to use Peek, Previous, Premise, etc.

Anyway, the pivot table was created from the Date and Order fields, with the Promise Date in the data cells.

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 20:03:15 GMT

Hi Alex,

Script will look somehow like this. (Adding the piece of code suggested by maxgro

[Aged Orders - March_temp]:

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as Date,

//Above line is for pulling date from file name

[Sales Doc.],

Item,

Upper(Status) as Status,

[Promise Date],

[Sales Doc.] & Item as [Order], bla bla... FROM bla bla

;

[Aged Orders - March]

load *,

if(Order=Peek(Order) and [Promise Date]<>peek([Promise Date]), 1, 0) as FlagChanged

Resident [Aged Orders - March_temp]

order by Order, Date;

drop Table [Aged Orders - March_temp];

After Reloading the script,

Add the expression 'sum(FlagChanged) ' to your pivot table.

Note : Premise is not a qlikview function, its a field name used here.

Thanks

krishna

Re: Count/distinct over time

Wed, 01 Jul 2015 20:13:28 GMT

So I load both of those right? The [Aged Orders - March]_temp and the second one as well?

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 20:20:36 GMT

[Aged Orders - March]

load *,

if(Order=Peek(Order) and [Promise Date]<>peek([Promise Date]), 1, 0) as FlagChanged

Resident [Aged Orders - March]_temp

order by Order, Date; //until here,it pulls fields from the temp table and performs the if() stmnt condition

drop Table [Aged Orders - March]_temp; //this piece drops the temp table from memory.

By writing the above script you will be loading only one table [Aged Orders - March].

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 20:24:31 GMT

If the above confuses you,Just Add

load *,

if(Order=Peek(Order) and [Promise Date]<>peek([Promise Date]), 1, 0) as FlagChanged;

above your existing script and reload.

[Aged Orders - March]:

load *,

if(Order=Peek(Order) and [Promise Date]<>peek([Promise Date]), 1, 0) as FlagChanged;

LOAD Date(Date#(Mid(FileName(),6,6),'MMDDYY')) as Date,

//Above line is for pulling date from file name

[Sales Doc.],

Item,

Upper(Status) as Status,

[Promise Date],

[Sales Doc.] & Item as [Order], bla bla... FROM bla bla

Re: Count/distinct over time

Wed, 01 Jul 2015 21:03:31 GMT

I believe I put it in correctly but it came up with 0... I put it in the pivot table with the Date and Order as dimensions and I put it in a straight table with the Order as a dimension and it came up with 0 both times.

I tried both ways in the script too and each came up with 0 for all the values for each Order...

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 21:07:05 GMT

Could you please attach the sample qvw?

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 21:33:48 GMT

reload the attached file and let me know.

Thanks

krishna

Re: Count/distinct over time

Wed, 01 Jul 2015 21:43:41 GMT

I think it worked! What did you change? I'll keep you posted as a use it further/add more data, but thank you so much!

Re: Count/distinct over time

Qrishna — Wed, 01 Jul 2015 21:47:58 GMT

Awesome.Please mark this post as 'Answered'.

Krishna

Re: Count/distinct over time

Thu, 02 Jul 2015 15:36:24 GMT

Hi Krishna,

So this works for the most part, but really only in the times when it says there are 3 promise date changes is when it gets messed up. When I just select the ones with 3 changes, and then go to the "do not open w/o filter" tab, you can see across that 3 is not the correct number for a few of those. I have really only found issues with a few of those ones. The ones with 2 promise dates don't seem to have issues, but I'm not sure.I am attaching another sample file to this post.I have tried to change Peek to Previous and it hasn't change anything.

Re: Count/distinct over time

Qrishna — Thu, 02 Jul 2015 16:41:40 GMT

Hi Alex,

I am having a hard time in opening the App and the app is too slow(figure out the expressions once which are responsible for this).

Could you please remove all the sheets except the one which we are discussing and send me the qvw again.?

Thanks

Re: Count/distinct over time

Qrishna — Thu, 02 Jul 2015 17:30:45 GMT

Alex,

As you can see the below screenshot, the expression is giving expected correct result.

check the script or the business rule where you might have forgot to apply for the on the promise date in the script.

note: even though i try to change the script, i cannot reload and check for the correctness unless i have the source data.

In the above screenshot, there are 3 date changes as expected.

thanks

krishna

Re: Count/distinct over time

Thu, 02 Jul 2015 18:24:02 GMT

What I was looking for is would be 1 change because the promise date changed from 3/9/2015 to 3/27/2015, so that would be one change. I'm not sure if it is possible to manipulate it to do that. I would try putting -1 at the end of the sum but then it would affect the ones that are already correct. However the script that you gave previously does work for when I am trying to track how many times the status changed! I am going to keep looking at the Promise Dates part though.

Re: Count/distinct over time

Qrishna — Thu, 02 Jul 2015 18:28:00 GMT

Check for the IDs which has date changes# 3.

As in the above picture, i see there are 3 date changes but m not sure what exactly is happening within data model as i cannot reload unless i have source data.