how to to make a straight table expression based on a row level?

[felipe_oliveira]

Hi Qlik masters

I have an issue where an expression from a straight table is not segregated correctly as following:

Table DWH

%Key	%Key2	Account	Dealer_code	Period	DWH.Program	DWH.Amount$	DWH.Year	DWH.Month
001	111	999-001	1111	Jan-2017	X	100	2017	Jan
001	333	666-002	1222	Mar-2017	Y	200	2017	Mar
002	555	888-001	1555	May-2017	Y	300	2017	May
003	555	555-001	1555	May-2017	X	100	2017	May
002	222	444-001	1111	Feb-2016	Y	200	2016	Feb
001	444	777-001	1444	Apr-2017	X	400	2017	Apr
003	444	222-001	1444	Apr-2017	Y	100	2017	Apr

%Key2 = AutoNumber(Dealer_code & Date(Period,'MMYY'))

Table Dealer

%Key2	App_id	Dealer_Name	Dealer_loc_code	Dealer_Date	Dealer.Program	Dealer.Amount$	Dealer.Year	Dealer.Month
444	1	B	1444	Apr-2017	Y	50	2017	Apr
111	3	A	1111	Jan-2017	X	30	2017	Jan
777	4	A	1111	May-2017	Y	30	2017	May
888	5	C	1333	May-2017	X	10	2017	May
555	6	E	1555	May-2017	Y	30	2017	May
999	7	C	1333	May-2016	X	20	2016	May
555	8	E	1555	May-2017	X	40	2017	May

%Key2 = AutoNumber(Dealer_loc_code & Date(Submittal_dt, 'MMYY'))

The link between the 2 tables is %Key2

I have a straight table where the dimensions are Dealer_Name and DWH.Program.

The expressions are:

Account on Book = count(Account)

On Book $ = sum(DWH.Amount$)

Submittal $ = sum(Dealer.Amount$)

I selecting the following (in yellow) on my list box

Dealer_Name		Dealer.Year		Dealer.Month
A		2017		Jan
B		2016		Feb
C				Mar
D				Apr
E				May

Current:

Dealer_Name	DWH_Program	Account on Book	On Book $	Submittal $
E	Y	1	300	70
E	X	1	100	70

Expected:

Dealer_Name	DWH_Program	Account on Book	On Book $	Submittal $
E	Y	1	300	30
E	X	1	100	40

I have tried many different things to have the Submittal $ segregated by row level like switching the Dimension from DWH_Program to Dealer_Program.

Also, I have tried many different expressions such as:

aggr(sum({$<DWH.Program=p(Dealer_Program)>} Dealer.Amount$),Dealer_Name, DWH.Program) Dealer

.Program

Does anyone can help me?

[kaushiknsolanki]

Hi,

You should also include the program field in your autonumber.

%Key2 = AutoNumber(Dealer_code & Date(Period,'MMYY')&DWH.Program)

%Key2 = AutoNumber(Dealer_loc_code & Date(Submittal_dt, 'MMYY')&Dealer.Program)

Regards,

Kaushik Solanki

[felipe_oliveira]

Thanks Kaushik for your prompt reply.

The suggested change fix the issue for Dealer E on May, but it messes up the others that were correct before making the change.

For example, if I do the change, for Dealer B that I had before

Dealer_Name	DWH_Program	Account on Book	On Book $	Submittal $
B	Y	1	100	50
B	X	1	400	-

turn into

Dealer_Name	DWH_Program	Account on Book	On Book $	Submittal $
B	Y	1	100	50

row for program X disappears.

I would like to show all programs linked to dealer B

[Anil_Babu_Samineni]

How about this for Submitted %

FirstSortedValue(Dealer.Amount$,Aggr(Sum(Dealer.Amount$), Dealer_Name, DWH.Program))

OR

FirstSortedValue(Dealer.Amount$,Aggr(Sum({<DWH.Program = E({<DWH.Program = {'X'}>}DWH.Program)>}Dealer.Amount$), Dealer_Name))

[felipe_oliveira]

Thanks Anil, but it didnt work.

[Anil_Babu_Samineni]

Try first one and show the image, How Report looks like?

[felipe_oliveira]

It looks like this:

Dealer_Name	DWH_Program	Account on Book	On Book $	Submittal $
E	Y	1	300	30
E	X	1	100	30

Submittal $ shows $30 twice