Sum Distinct in Set Analysis

[Xytras]

Hallo Everybody,
I'm trying to do a Sum Distinct in a bar chart in Set Analysis. For security matter I cannot do it in the Load Script.
Based on excel table attached, I need to sum amount in column G only once for each duplicated lines.
Below is the formula I used. Is it right?

Sum(
Distinct {$<
GLAccount,
Category,
BusinessGroup,
Division,
ProductLine,
ValueInGroupCurrencyUSD,
Date
>}
//{$<HFMGLData.FLAG={'MQ'},HFMGLData.DataToShow={'X'},HFMGLData.FiscalYear={">=$(MaxHFMDateYearMinus1)"}>}
HFMGLData.ValueInGroupCurrencyUSD)

I also noticed that for the fields which are part of the Sum Distinct, the related filter is not working but if I try to remove one of the fields from the Sum Distinct it works again.
Hope you can confirm the formula is right and why I cannot use a filter for a field present in the list of Sum Distinct.
Thanks in advance,
Ivan

[Anil_Babu_Samineni]

@Xytras Perhaps remove Distinct from your expression or you can replace distinct with Total.

[stevedark]

Hi @Xytras 

Carrying duplicate values in your data model is pretty nasty, and likely to trip someone up at some point. If you are able to remove the duplicates in the load script then that would be a much better way of doing things.

The DISTINCT function doesn't work with SUM in the Qlik front end, it only works with COUNT.

What you need to do is specify a set of dimensions, find the max value for each of those sets of dimensions, and then add those max values. This will effectively give you the value in the column you added in Excel.

To group by fields and then work on the results of that set of dimensions you need to use the AGGR function.

It will be something like:

sum(aggr(max(HFMGLData.ValueInGroupCurrency),BusinessGroup,Category,Date,Division,GLAccount,ProductLine))

You can add any set analysis you need within the max statement.

The first chunk of set analysis you specify in your message is not valid, as it lists fields but has no operator. It is not required anyway.

Hope that helps,

Steve

[Xytras]

Thanks to both of you.. Formula you provided seems working in model with limited set of data but, unfortunately, when I use the full set I'm still seeing some discrepancy. I suspect that records used are not all unique.

Thanks again for your prompt replies and cooperation.

Ivan