Problem of calculating unique values

[SonyaBeketova28]

Hello. I need your help, I will be grateful if you help me solve my problem.

I load data from Excel files that are on the server using a script in the data download editor. The file has the fields "checkname", "new", "active" (screenshot below) and the date is collected from three fields through makedate into the "DateField". My task is to count the sum of "new" and "active", grouping by "checkname" and "DateField". But there is a problem: lines with the same "checkname" are repeated many times, and I need only unique "checkname" values to be used when summing "new" and "active".

Thus, I add the “DateField” variable to the groups,

In the "Line" field I enter the expression "=If(Index([checkname], 'AR')>0 and Index([checkname], 'KR')>0, 'AR-KR', If(Index([checkname], 'AR')>0 and Index([checkname], 'VIS')>0, 'AR-VIS', If(Index([checkname], 'KR')>0 and Index([checkname], 'VIS')>0, 'KR-VIS', If(Index([checkname], 'AR')>0 and Index([checkname], 'KR')=0 and Index([checkname], 'VIS')=0, 'AR', If(Index([checkname], 'KR')>0 and Index([checkname], 'AR')=0 and Index([checkname], 'VIS')=0, 'KR', If(Index([checkname], 'VIS')>0 and Index([checkname], 'AR')=0 and Index([checkname], 'KR')=0, 'VIS')))))) " (I need the "checkname " to be grouped by sections and use «Index» so that it is case sensitive);

And in the measures I write the expression “=Sum(distinct {<[checkname]>} [new] + [active])”.

But Qlik can’t correctly calculate the amount for me using unique expressions

There is also a condition that only [checkname] must be unique, while [new] and [active] may not be unique. But when I use “distinct”, Qlik show the amount of unique checkname, new and active (all riables are unique)

I also tried using the functions "If(Index(Aggr(Concat(distinct [] , '... '), [] ), '') > 0 " and "If(Index(Aggr(Only([]), [] ), '...') > 0", but nothing works correctly.

Help me please

[WaltShpuntoff]

Sonya,

There is no screenshot attached.

I suggest that when you load your data, you create a type field that you can use as a dimension.

I am unclear on what metric you are trying to calculate - counts, or sums or both?.

If you need to collapse the multiple types of records, you can do something like autonumber(CheckName & | CheckType, 'check') and that will return a single value for each combination of records.

ws

[SonyaBeketova28]

I have the original excel file, screenshot below.

I'm making a bar chart based on it, screenshot below  Field “DataField” is “Date(MakeDate(year, month, day), 'DD-MM-YYYY') as DateField”.

 Checkname is:

=If(Index([checkname], 'AI')>0 and Index([checkname], 'SS')>0, 'AI-SS', 

If(Index([checkname], 'АI')>0 and Index([checkname], 'MEP')>0, 'АI-MEP',

If(Index([checkname], 'SS')>0 and Index([checkname], 'MEP')>0, 'SS-MEP',

If(Index([checkname], 'AI')>0 and Index([checkname], 'SS')=0 and Index([checkname], 'MEP')=0, 'AI',

If(Index([checkname], 'SS')>0 and Index([checkname], 'AI')=0 and Index([checkname], 'MEP')=0, 'SS',

If(Index([checkname], 'MEP')>0 and Index([checkname], 'AI')=0 and Index([checkname], 'SS')=0, 'MEP'))))))

 Summary of active collisions is 

“=Sum(distinct {<[checkname]>} [new] + [active])”, but in this formula, distinct is applied to both “checkname”, and “new”, and “active”, and it is necessary that only to “checkname”. 

Because "new" and "active" may not have unique meanings

 

[qv_testing]

While loading you can use Distinct checkname, then it will be filtering the data.

LOAD Distinct checkname,
New,
Active,
Year,
Month,
Day
FROM
[C:Data.xlsx](ooxml, embedded labels, table is Sheet3);

[SonyaBeketova28]

But if I write “Distinct” after “Load”, then it will apply to all subsequent loaded units, won't it? But I need it to apply only to checknames.