Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ignoring dimension in set analysis?

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:

       

DateOrder dateOrderNumberOrderRowNumberArticleNumberInvoiceNumberInvoiceRowNumberTypeInvoiceStatus
2016-01-01 6545165451001854621 1
2016-01-01 6545165451002789874 1
2016-01-032016-01-0165451 85462110023548100235480012

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

4 Replies
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);


Capture.PNG

Not applicable
Author

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

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

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.

View solution in original post