Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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);
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
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().
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.