Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Define Dynamic Dimension

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..

9 Replies
Highlighted

Re: Define Dynamic Dimension

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

Highlighted
Not applicable

Re: Define Dynamic Dimension

Thanks Sunny

suppose that I have the following tables

Orders

Order_numberDescriptionIssuer
1aaaa
2bbbb
3cccc

Status history

   

IDOrder_numberStatusDate
11Received 01/01/2016
21under process02/01/2016
32Received 02/01/2016
43Received 02/01/2016
52under process03/01/2016
63under process03/01/2016
71Completed03/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..

Highlighted
MVP & Luminary
MVP & Luminary

Re: Define Dynamic Dimension

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

Highlighted
Master III
Master III

Re: Define Dynamic Dimension

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)

Highlighted

Re: Define Dynamic Dimension

Try this expression:

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

Capture.PNG

Highlighted
Not applicable

Re: Define Dynamic Dimension

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.

Highlighted
Not applicable

Re: Define Dynamic Dimension

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..

Highlighted
Not applicable

Re: Define Dynamic Dimension

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.

Highlighted

Re: Define Dynamic Dimension

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