Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
anthony_kinsell
Creator
Creator

Aggregation / Counting in a script

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,

 

17 Replies
anthony_kinsell
Creator
Creator
Author

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.SalesUnitsGrab.PNG

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
anthony_kinsell
Creator
Creator
Author

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

Hi Arthur,

Yes all 3 entries are members of 6101

Arthur_Fong
Partner - Specialist III
Partner - Specialist III

Is that column an expression like:

 sum(UnitSales)? 

Maybe try:

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

anthony_kinsell
Creator
Creator
Author

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