aggr / count function problem

[Anonymous]

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

[Anonymous]

hi onno, sorry i missed out your instructions.

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

thanks

[sunny_talwar]

Check the attached

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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

[Anonymous]

you are a genius! and my angel!

that is exactly what I need...

thank you so much for your time....

[Anonymous]

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

[sunny_talwar]

May be try this

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

[Anonymous]

amazing!

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

thank you so much!!!!