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