How to get the right total with Distinc in expression ?

[#1]

Hi everybody , I need your help please.
I have a table with a Budget By Item Family on a specific Month and Year.

Year	Month	Item Family	Item	Budget
2016	05	A00	A00-A11	100 $
2016	05	A00	100-A12	100 $
2016	05	A00	A00-A13	100 $
2016	05	B00	B00-B01	235 $
2016	05	C00	C00-C01	100 $

In my Report , I would like to have all Item Family with the Budget , something like this :

Year	Month	Item Family	Budget
2016	05	A00	100 $
2016	05	B00	235 $
2016	05	C00	100 $

to do that I created an expression : Sum(distinct [(Budget)])

the problem is that the total of my expression is 335 $ . This is due to DISCTINCT used in the expression, How can I have the right toal ?

[swuehl]

I think it would be best to separate your budgets values in a table per Year, Month and Item Family (and then use just Sum(Budget) )

If you don't want to change your model,try

=Sum(Aggr( Only(Budget), Year, Month, [Item Family]))

[Gabriel]

Hi,

This is just a thought. I will look to do something in the script like this

Data:

LOAD Year,

     Month,

     [Item Family],

     Item,

     Left(Budget,3)                                                    AS Budget

FROM

[https://community.qlik.com/thread/216646]

(html, codepage is 1252, embedded labels, table is @1);

DataTrans:

LOAD

    *,

    IF([Item Family] <>Peek([Item Family]) ,1,0)                    AS [Item Family Trans]       

    Resident Data;

    DROP Table Data;

Then have an Expression like this in the chart

=Sum({<[Item Family Trans]={1}>} Budget)

Hope this helps

[anagharao]

Try this.