Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

patrickvos
New Contributor II

Table with date and at days to this date

Hi all,

I have the following question.

I'am building an dashboard that makes the order supply backlog clear.

Therefore I want to count the days of the priority of the order with the creationdate.

In the sheet is the date of creation 07.01.2017, priority of the order is D. D stands for 20 days.

So the outcome must be 27.01.2017.

Can anyone help me with this task?

Count dates with a number.JPG

Many thanks,

gr Patrick

7 Replies

Re: Table with date and at days to this date

What calculation you used for?

patrickvos
New Contributor II

Re: Table with date and at days to this date

Calculations used.JPG

Re: Table with date and at days to this date

Why E needed 3 times in your statement?

patrickvos
New Contributor II

Re: Table with date and at days to this date

This is in a test fase. So thats wrong and i know this. I have changed it to the wright values.

capture 3.JPG

patrickvos
New Contributor II

Re: Table with date and at days to this date

Bud the problem i have is to count the date with prio D that stands for 20 days.

=count({$<[Created On]+[priority,'D','20']>}) bud this is wrong i know.

Please advise in this question..

gr Patrick


qliksus
Valued Contributor

Re: Table with date and at days to this date

Not sure if I have understood you correctly do you want to count the order which is backlog for n number of days ? for that to happen there should be one more date apart from creation date which gives the info of how long the order is held right?

Count ({<Inventorydate= {"<=date(max(Creationdate)+Prio)"}>} order)

patrickvos
New Contributor II

Re: Table with date and at days to this date

Hi All,

I managed to get the correct formula by my self.

 

This is the dimension expression:

=IF(([Object status] = 'CLSD' OR [Object status] = 'TECO'), 'Order Ready', 
IF(Date([Created On]
+
if (isnull(Priority
), '0',
IF (Priority
= 'C', '90',
IF (Priority
= 'D', '20',
IF (Priority
= 'E', '7',
IF (Priority
= 'F', '3',
IF (Priority = 'G', '1',)))))),'DD.MM.YYYY') - Today() <0, 'Backlog', 'In Control'))

 

The background collor is programmed this way:

=IF(([Object status] = 'CLSD' OR [Object status] = 'TECO'), LightGreen(),
IF(Date([Created On]+
if (isnull(Priority), '0',
IF (Priority = 'C', '90',
IF (Priority = 'D', '20',
IF (Priority = 'E', '7',
IF (Priority = 'F', '3',
IF (Priority = 'G', '1',)))))),'DD.MM.YYYY') - Today() <0, LightRed (),LightGreen ()))

 

Now it is possible to get the wright information of the balance score cart:

It wil look like this:

Many thanks for helping me out.

gr Patrick

Community Browser