topic Re: aggr / count function problem in QlikView

aggr / count function problem

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello QLikers!

Hope you can help me with my qliview problem. I am not a programmer although I have undergone qlikview training for 2 days. I basically can do simple formula but when it becomes complex I am a bit perplexed. I don't know what to do next, that is why I am giving this to the expert out there and hopefully help me progress in may project.

Attached is the sample of my data and would appreciate any help from you guys!

BRIEF PREVIEW OF MY DATA

There are 3 sheets on it:

PIVOT - is the weekly data that I use in the load script

SD DRILLDOWN - is where I sort the number versus a set of criteria

DATA - is the end project of what my data would look like

thanks in advance..

Rita

Re: aggr / count function problem

sunny_talwar — Sat, 10 Jun 2017 09:56:03 GMT

May be these

Total Count

Count({<Week={$(=MaxString(Week))}>} DISTINCT DEVICE)

Single Device

Count({<Week={$(=MaxString(Week))}, DEVICE = {"=Count(DEVICE) = 1"}>} DISTINCT DEVICE)

<6 Hrs

Count({<Week={$(=MaxString(Week))}, DEVICE = {"=Count(DEVICE) = 1"}, HOURS = {'<=6'}>} DISTINCT DEVICE)

>24 Hrs

Count({<Week={$(=MaxString(Week))}, DEVICE = {"=Count(DEVICE) = 1"}, HOURS = {'>6'}>} DISTINCT DEVICE)

Re: aggr / count function problem

Anonymous — Sat, 10 Jun 2017 10:52:15 GMT

Hi Sunny,

I've tried your suggestions....

good thing to note is that it reflect the current week which is wk2..

unfortunately the numbers are not...

the numbers should be like this....

any other ideas?

thanks for your fast reply....

Re: aggr / count function problem

sunny_talwar — Sat, 10 Jun 2017 13:14:45 GMT

Might help if you can share a sample app where we can test it out. Right now the Excel file you shared only had data for Week2... which makes it difficult to test. May be with some more data, we might be able to figure something out.

Re: aggr / count function problem

Anonymous — Sat, 10 Jun 2017 14:15:47 GMT

forgive my ignorance but I could not seem to find the attached file button here..

Re: aggr / count function problem

oknotsen — Mon, 12 Jun 2017 06:51:08 GMT

Click on reply.

In the top-right corner, click on "Use advanced editor".

In the bottom-right corner, click on "Attach".

Re: aggr / count function problem

sunny_talwar — Mon, 12 Jun 2017 10:58:56 GMT

You can also look here

Uploading a Sample

Re: aggr / count function problem

Anonymous — Tue, 13 Jun 2017 00:40:51 GMT

HI Sunny, thanks for that.

I have already attached my sample file, pls. advise if you are able to view it and hope you can help me work out with my project.

thanks a lot!

Re: aggr / count function problem

sunny_talwar — Tue, 13 Jun 2017 07:23:14 GMT

What is the issue here? Is there a specific object which isn't working?

Re: aggr / count function problem

Anonymous — Tue, 13 Jun 2017 07:56:02 GMT

the above sample is the WK6 data

what I would like to do is, even if I don't choose a particular week , the numbers will reflect the latest WEEK data....

See attached for sample..

Re: aggr / count function problem

Anonymous — Tue, 13 Jun 2017 08:03:24 GMT

hi onno, sorry i missed out your instructions.

just to let you know that I have used your tip.

thanks

Re: aggr / count function problem

sunny_talwar — Tue, 13 Jun 2017 08:06:58 GMT

Check the attached

Re: aggr / count function problem

Anonymous — Tue, 13 Jun 2017 08:34:31 GMT

Perfect!

I would like to add another problem if I may please bear with me a little longer.

I have added 3 columns in the table but the numbers are not exactly what I want.

Re: aggr / count function problem

sunny_talwar — Tue, 13 Jun 2017 08:38:26 GMT

I think you might not have added WEEK to your aggr dimension

Re: aggr / count function problem

Anonymous — Tue, 13 Jun 2017 11:58:56 GMT

you are a genius! and my angel!

that is exactly what I need...

thank you so much for your time....

Re: aggr / count function problem

Anonymous — Fri, 16 Jun 2017 00:57:29 GMT

Hi Sunny,

It's me again.. I wanted to make a follow up question with my project.

If I need to get the sum total of HOURS base on the result of aggr formula, how do I modify this expression?

SUM(Aggr(If(Count(DEVICE)=1,1,0),DEVICE,WEEK))

thanks

Rita

Re: aggr / count function problem

sunny_talwar — Fri, 16 Jun 2017 01:00:19 GMT

May be try this

SUM(Aggr(If(Count(DEVICE)=1,Sum(HOURS)),DEVICE, WEEK))

Re: aggr / count function problem

Anonymous — Fri, 16 Jun 2017 01:19:03 GMT

amazing!

I am dumb in qlikview formula, always confused where and what to insert....

thank you so much!!!!