Statusupdates on a day-by-day basis?

[annanirvin]

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

[ahaahaaha]

Hi Anna,

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

Regards,

Andrey

[annanirvin]

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

[ahaahaaha]

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

[annanirvin]

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

[cheenu_janakira]

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

[cheenu_janakira]

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?

[cheenu_janakira]

Have a look at the attached script.

[annanirvin]

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

[cheenu_janakira]

Hi Anna,

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

Kr,

Cheenu