topic Re: Statusupdates on a day-by-day basis? in QlikView

Statusupdates on a day-by-day basis?

annanirvin — Wed, 25 Nov 2020 16:16:04 GMT

Hi.

I store my data in one single table as seen below. Is it possible to follow the day-by-day changes of specific OrderId:s so that the users by selection can see which ones that have proceeded from ‘Ordered’ to ‘Shipped’ and so on?

Anna

Re: Statusupdates on a day-by-day basis?

ahaahaaha — Sat, 25 Feb 2017 14:57:18 GMT

Hi Anna,

In what form you would like to see dashboard for the user?

Regards,

Andrey

Re: Statusupdates on a day-by-day basis?

annanirvin — Sat, 25 Feb 2017 15:29:06 GMT

Hi Andrey.

Thanks for your reply. Well, I haven’t actually decided since I wasn´t sure if it can be done at all. Anyway would do, I suppose.

Best regards, Anna

Re: Statusupdates on a day-by-day basis?

ahaahaaha — Sat, 25 Feb 2017 16:35:04 GMT

Full freedom for fantasy.Let's try to start to create. Maybe something like this?

What other information would you like to see? In what form?

Regards,

Andrey

Re: Statusupdates on a day-by-day basis?

annanirvin — Sun, 26 Feb 2017 10:22:42 GMT

Thank you. What I´m really after though, is a function that evaluates which OrderID:s has changed their substatus compared to a specific day or period, for example "from 2017-02-19 to 2017-02-20" or "the last three days". (I have approximately 12 000 OrderID:s to evaluate.) For a start I´d like to be able to see the specific OrderID:s in for example a straight table.

Any idea of how to solve this?

Regards, Anna

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 11:50:10 GMT

Hi Anna,

Is there something that prevents you from creating columns of status dates for one record rather than having them in rows?

I would go about it, such as:

Statuses:

LOAD * INLINE [

Statuses

Ordered

Shipped

Delivered

];

FOR i=1 to NoofRows('Statuses')

LET vStatuses= PEEK('Statuses', $(i)-1, 'Statuses');

IF ALT(NoofRows('FinalTable'), 0) > 0 THEN

SET vLeftJoin= 'LEFT JOIN(FinalTable)';

END IF;

FinalTable:

$(vLeftJoin)

LOAD

OrderID,

Date as $(vStatuses)_Date

FROM [Whatever the source]

WHERE Substatus = '$(vStatuses)';

NEXT i;

DROP TABLE Statuses;

SET vLeftJoin = '';

Hope this makes sense. As soon as you have your status dates in columns it becomes much easier to take date differences and making bucket/interval values.

You might also want to check GENERIC function in Qlik if you are not already familiar with it.

Kr,

Cheenu

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 11:52:42 GMT

I just saw from your raw data table above that an OrderID can have multiple "shipped" dates. Is this just a 'demo' sample? Can an order have multiple dates for one status?

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 12:08:20 GMT

Have a look at the attached script.

Re: Statusupdates on a day-by-day basis?

annanirvin — Sun, 26 Feb 2017 12:30:49 GMT

Hi Cheenu,

Thank you for helping me out here. Your solution would absolutely work but is not quite what I´m looking for in this case. My main concern is to be able to see how many OrderID:s that has proceeded to the next level in the process on a particular day/period and also to be able to identify these specific OrderID:s.

Kind regards, Anna

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 12:49:22 GMT

Hi Anna,

In the attached QVW, could you not use set analysis on any of the fields generated?

Kr,

Cheenu

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 12:54:35 GMT

Are you looking for a kind of Sankey chart? I'm still not sure (as per Andrey above) how you want to represent this for your users. Can you give more details?

Re: Statusupdates on a day-by-day basis?

cheenu_janakira — Sun, 26 Feb 2017 13:17:11 GMT

And what about a "best status" column, as attached?

Re: Statusupdates on a day-by-day basis?

annanirvin — Sun, 26 Feb 2017 14:33:00 GMT

This is very helpful, thanks a lot!