Hello! I have a task: make some math-calculated fields in load script. First, i'm loading sourse table:
applymap('DomainMap',i_domainID,null()) as domain,
date(floor(ConvertToLocalTime(25569 + (`i_dateCreate` / 86400), 'GMT+03:00'),1)) as [День создания],
date(floor(ConvertToLocalTime(25569 + (`i_dateClose` / 86400), 'GMT+03:00'),1)) as [День закрытия],
date(monthstart(date(floor(ConvertToLocalTime(25569 + (`i_dateClose` / 86400), 'GMT+03:00'),1))),'MMM YY') as [Месяц закрытия],
ApplyMap('UsersMap',i_domainID&i_responsibleUserID,null()) as user,
FROM [lib://QVD (win-lng2sl4osbd_qlik.acc)/amoCRM\leads.qvd] (qvd) where i_dateCreate>=$(vPastMonth) or i_dateClose>=$(vPastMonth);
after that i've create control table to confirm my load is correct. This is control line, that displayed than current manager have one closed deal on 20 000 on feb-2017:
After that i'm loading table with aggregation:
sum(i_sum) as ThisMonthSum
Resident Leads where i_statusID=142 and i_dateClose>=$(vThisMonth) group by domain, user;
drop table Leads;
As result i'm espect to see same info, but in number format. But it shows me results, multiplied on 3:
i'm created special field in load script, that counts amount of deal id: distinct and no distinct. And we see, that one closed deal for this managers counts three times. I cant get what wrong with my formula.
All managers for this domain have multiplied result. there is three managers total in this domain
but in another domains different between source data and calculation may be another.
What is wrong?