topic Re: Classifying by size expression in QlikView

Classifying by size expression

Thu, 28 Feb 2013 18:01:19 GMT

Hello,

Personnel how do I sort by selling a dimension?

dimensions: Store, Vendor, customer code and customer name

expressions: Rank and sale

In dimension seller have a condition, just shows sellers that sell on the selected date.

The size is what I classify the customer name. Have you tried to sort by the expression size and customer name can not.

Can anyone help me?

Thank you.

Re: Classifying by size expression

Thu, 28 Feb 2013 18:40:24 GMT

Your issue is with your dimensions. If Customer Name is your lowest dimension, sorting by the highest sale will sort your sales from highest to lowest inside of Customer Name. You might want to try making customer code and customer name expressions instead of dimensions.

Re: Classifying by size expression

Fri, 01 Mar 2013 19:20:26 GMT

Other_peoples_shoes,

Below the print and the codes used to mount the screen.

The red marking is a classification of the sale value (right column).

Can you help me?

Thank you.

DIMENSIONS:

* Store,

* IF(MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) >= DATE(Ano1) AND MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) <= DATE(Ano2),NOME_VENDEDOR),

* Cliente code,

* Cliente

EXPRESSIONS:

Column Rank:

RANK(SUM(IF(

MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA >= DATE(YEAR1)

AND

MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),SALES)),1,1)

Column Sales:

SUM(IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

AND

MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),SALES)

Column number of pieces:

SUM(IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

AND

MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),PIECES)

Column amount sales:

COUNT(DISTINCT IF(MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) >= DATE(YEAR1)

AND

MAKEDATE(YEAR_VENDA,MONTH_VENDA,DAY_VENDA) <= DATE(YEAR2),TICKET)

SORT EXPRESSIONS:

SUM(TOTAL<NOME_VENDEDOR>IF(

MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) >= DATE(Ano1)

AND

MAKEDATE(ANO_VENDA,MES_VENDA,DIA_VENDA) <= DATE(Ano2),SALES))

Re: Classifying by size expression

Fri, 01 Mar 2013 20:09:16 GMT

Ok, you have several problems.

1. Dimensions.

The way that QlikView sorts things is very strange, and so it behaves differently then you would expect.

Let me give an example:

If D_1 is a dimension, and you tell QlikView that you want to sort this table by Rank:

D_1 Rank V_1

A 1 apple

A 3 pear

B 4 banana

B 2 grape

You will not get Rank = 1 2 3 4. Sort functions inside of your dimensions. You will get:

D_1 Rank V_1

A 1 apple

A 3 pear

B 2 grape

B 4 banana

as QV will sort each Dimension by rank.

This table, on the other hand, will not sort at all:

D_1 D_2 Rank V_1

A A 1 apple

A B 3 pear

B A 4 banana

B B 2 grape

So in order to get any sorting done, you will have to remove some dimensions. Consider making Client Code an expression, rather than a dimension.

Re: Classifying by size expression

Fri, 01 Mar 2013 20:14:10 GMT

2. MakeDate. Telling QlikView to look up 3 different columns and compute a function every time that you try to do everything will make this application work very, very slowly. If you are loading from raw data, use

load *,

makedate(year_venda,month_venda,day_venda) as venda_date in your load script to calculate the dates once, when you load the data, and store it thereafter. If you are loading from .qvds, go back to edit the file that creates the qvd.

Trust me, your program will work much better.

Re: Classifying by size expression

Fri, 01 Mar 2013 20:17:28 GMT

3. If statements.

If your program is still running too slowly, consider using set expressions instead of if statements for your calculations. If statements are easier to program, but harder for QV to calculate.

I think that the equation you want to use is: sum({$<venda_date={>year1<year2}>}sales), though I'm not 100% certain. It should keep things running much more smoothly.