Aggregation / Counting in a script

[anthony_kinsell]

Hi,

I'm having some trouble getting the script correct to create a sales number per month from my data.

Basically I need to count the distinct 'TenantRef' in the data where the 'Account' is 6101.

I know this should be simple but keep getting invalid function errors - can anyone help?

I've tried the following type of script:

If(Account = '6101', Count(DISTINCT TenantRef)) as UnitSales,

[anthony_kinsell]

Thanks Tim,

Really appreciate the help - almost there I think - it seems the formula isn't counting the distinct TenantRefs though - seems to be counting them all.

I've tried to attach a snip from a straight table of the data - all figures should be 1 - as it's counting the distinct value - however you'll see that in this case the highlighted line is 3. The invoice was entered incorrectly initially, this was credited off & then reentered correctly - all under the same tenant so value should be 1.

[Brett_Bleess]

Anthony, Tim is likely the better resource here, but I think you need some logic to check if there are multiple transactions in those cases and only load the line you want...  Not sure if that will make sense or not, the Peek() function would be part of that solution I think, but I am not a very good developer just FYI.  The only other thing I can offer is the following link to the Design Blog area of the Community, there are hundreds of mostly how-to topics that may be of some help.  

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

I am not exactly sure what you need at this point, otherwise I would have done some searching and give you something more specific from there, felt it was better to let you poke around on your own in this case, so I do not confuse you! 🙂  Hopefully this will help, but maybe Tim will be back with something more helpful or someone else will chime in on things.

Regards,
Brett

[anthony_kinsell]

Thanks Brett,

I thought by putting DISTINCT into the formula this was saying only count the DISTINCT TenantRefs - that's how it works in a formula in a chart - may be different in script.

So for example if I have a TenantRef of HAN01/01/01 appear 3 times in 1 month (like in the example - an invoice entered incorrectly - credited & entered correctly) - then it only counts as 1 not 3 - that's what I'm trying to achieve.

Anthony

[Brett_Bleess]

Anthony, yeah, sorry, you are correct there, but there must be something in the data model that is causing things to think those are all unique, i.e. something in the 'full' record that makes each one unique, in that case, I think the distinct would do what it is doing in your case, but I could be wrong, again, I am not too swift on the development side, I know enough to be pretty dangerous, hopefully someone else with better dev skills can have a look and see if they concur etc.

Cheers,
Brett

[Arthur_Fong]

Your script is reading:

If(Account = '6101', Count(DISTINCT TenantRef)) as UnitSales.

Is <HAN02/01/70> (credited off & then reentered correctly) a member from Account 6101?

[anthony_kinsell]

Hi Arthur,

Yes all 3 entries are members of 6101

[Arthur_Fong]

Is that column an expression like:

 sum(UnitSales)? 

Maybe try:

sum(aggr(count(distinct UnitSales),<dim1>,<dim2>,<dim3>))

[anthony_kinsell · Accepted solution]

Hi Tim,

I solved this by creating a new source to count the unit sales - basically as you suggested above from the IncomeTemp table.

I then concatenated this into my fact table - worked perfectly.

Thanks again for your assistance