Define Dynamic Dimension

[#1]

Hi everybody..

Probably you went through this problem before. I hope you would be able to share your experience with me or propose a solution to it.

The problem:

for simplicity let us say I have three tables

1- Order Fact table

2- Date Dimension table

3- Order status Dimension table

My problem is when I count the number of orders per order status within a period. Qlickview will double count the orders with each status. Also for the table box I get only the orders that have the selected status with in the selected period.

I need to know, is there a way to define a dynamic dimension where it can be used to list the orders or count them based on their last status in the selected period. 

any other solution is highly welcomed as well..

your effort is appreciated..

[sunny_talwar]

It would be much easier to help if you can provide a sample with expected output.

[#3]

Thanks Sunny

suppose that I have the following tables

Orders

Order_number	Description	Issuer
1	aaa	a
2	bbb	b
3	ccc	c

Status history

ID	Order_number	Status	Date
1	1	Received	01/01/2016
2	1	under process	02/01/2016
3	2	Received	02/01/2016
4	3	Received	02/01/2016
5	2	under process	03/01/2016
6	3	under process	03/01/2016
7	1	Completed	03/01/2016

I need to build a chart to count the number of orders per status. using the chart wizard it will present to me the following:

3- Received

3- under process

1- Completed

while it should be

2- under process

1- Completed

what I need to count based on last status .

--------------------------

The part related to the table box we could figure it out later on..

Thanks in advance..

[marcus_sommer]

You may need to differentiate if you want to count the orders from the fact- or from status-table which also meant you shouldn't rather try to count over a KEY-field else to count per another field maybe extra created for this reason, like:

OrderID as OrderIDFromFact

...

OrderID as OrderIDFromStatus

If you not to differentiate this and you could apply count(DISTINCT OrderID) you might avoid the above mentioned extra fields.

- Marcus

[Digvijay_Singh]

Not sure the real intent from the prob description, but it looks like you want to count the order with the latest status. You may need to use set analysis to filter orders with max(date) something like Count({<Date={"$(=Max(Date)"}>}Orders)

[sunny_talwar]

Try this expression:

=Count(DISTINCT {<ID = {"=Status = Aggr(FirstSortedValue(TOTAL <Order_number> Status, -ID), Order_number, ID)"}>}Order_number)

[#7]

it sounds good Sunny..

however,, could you please breakout the code and explain its concept so that I could reuse it..
I have tried to do it myself but I could not

Marcus, Digvigay.. Thanks for your participation,, I believe Sunny has delivered what I'm looking for.

[#8]

Also, could you provide new version of the code where you use the Date to find last record instead of the ID.

Your effort is highly appreciated.

regards..

[#9]

I read a lot about aggr and firstsortedvalue functions. I believe using them along with set analysis would solve my problem. sunindia‌ could you please explain to me the concept of your script. Using your script would solve my problem, however, it need to be modified to use the Date instead of ID since actually I do not have it.

In my reading I have noted that firstsortedvalue returns nothing if there are more than one value at the top, I believe we will face this problem as well. so let us assume that the order could have more than one status in the same date. In this case we have to pick any one of them.

[sunny_talwar]

Hi Fadhel, I have your question on my radar and will def. respond back to you sometime today. Apologize for the delay