Ignoring dimension in set analysis?

[#1]

Hi,

I need help with a set analysis which is causing me trouble. This kind of question probably has been answered before, but I am not sure what kind of solution to search for.

I have a table containing orders and invoices. Below is an example:

Date	Order date	OrderNumber	OrderRowNumber	ArticleNumber	InvoiceNumber	InvoiceRowNumber	Type	InvoiceStatus
2016-01-01		65451	65451001	854621			1
2016-01-01		65451	65451002	789874			1
2016-01-03	2016-01-01	65451		854621	10023548	10023548001	2	Shipped

The three post above shows the same customer order. The order contains two order rows which were received in 2016-01-01.

On 2016-01-03 the first order row was shipped. The second order row has not yet been processed and therefore only exists as an order row in the database.

Now I want to create a chart showing the number of order rows that has been received per date and the number of order rows that has been shipped. I don’t care which date the order row has been shipped, I only want to know whether it has been shipped or not. I want the chart to look like this:

Date	# order rows received	# shipped order rows
2016-01-01	2	1

For counting the number of order rows received I use the following:

count(OrderRowNumber). 

For counting the number of shipped order rows I use a set analysis that currently looks like this:

count({<InvoiceStatus = {Shipped}>} InvoiceRowNumber)

This kind of works, it will show the correct number of received order rows for that date, but it will present the shipped order row on the wrong date (2016-01-03) which is not what I want. I guess in this set analysis I would want QlikView to disregard the Date dimension for rows with Type 2. Does that make sense?

I am open to any suggestions on how to solve this. Thanks in advance!

Regards

Anders

[sunny_talwar]

May be change a script a little and use order date as your dimension:

Table:

LOAD Date,

    If(Len(Trim([Order date])) = 0, Date, [Order date]) as [Order date],

    OrderNumber,

    OrderRowNumber,

    ArticleNumber,

    InvoiceNumber,

    InvoiceRowNumber,

    Type,

    InvoiceStatus

FROM

[https://community.qlik.com/thread/201311]

(html, codepage is 1252, embedded labels, table is @1);

[#3]

Hi Sunny T,

Thanks for the advice! Yes, a solution like that could probably work. But I do believe that it should be possible to do with a set analysis as well.

Regards

Anders

[morganaaron]

Think that would be difficult as you have Date as your dimension and you won't be able to analyse row by row with set analysis.

If you have fields determining when to look at Date and when to look at Order Date, you could use something like:

=If(Type=1, TrueDate, OrderDate)

as a calculated dimension.

I'd probably advise something along the lines of Sunny's answer though - filling the Order Date so that it's a consistent field, either by using his method or looking at using Peek().

[swuehl · Accepted solution]

As already mentioned above, the set analysis will be evaluated once per chart, not per dimension value. I agree with Sunny, his solution or something along the lines of making a little change in the data model would probably will be the best solution (as far as we know and understand your requirements, but there are probably more constraints to take care of).

If you don't want to change your data model, try this:

1) Use OrderNumber instead of Date as dimension in a straight table chart

2) Create three expressions:

=Date(Min(Date))

=count(OrderRowNumber)

=count({<InvoiceStatus = {Shipped}>} InvoiceRowNumber)

3) If you want, change the presentation of your table chart, hide the OrderNumber dimension, change the order of your columns as you like etc.