topic Formula guidebook or help in QlikView

Formula guidebook or help

Thu, 09 Dec 2010 15:13:43 GMT

Hi all,

I am a bit new to Qlikview, however I try to make nice reports. Does anybody have a link where I can find explanations about the formulas?

Such as this one?

count(distinct{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'}>}if((agg(sum([Sold Value SRP]),[Opportunity ID])>50000),[Opportunity ID])

I am trying to understand these, but I am not succeeding, therefor my own formulas don't work.

Right now I am trying to get the number of unique customers [ID_RELATIE], that have as [KLANTGROEP] the value 'Strategic Account'

Should be an easy formula, but I don't get the proper number.

Thanks!!

Formula guidebook or help

marcel_olmo — Thu, 09 Dec 2010 15:26:08 GMT

Hey wouter,

could you be more specific about what you don't understand exactly about the formulas?

Qlikview has a useful and complete "help" tab, that should be enough to understand that.

Specially with the "set analysis" stuff.

Hope it helps!

Formula guidebook or help

Thu, 09 Dec 2010 15:30:10 GMT

Well, I don't understand the syntax.

Is this a correct formula (Probably not, since it doesn't give me the output):

count (distinct {$<KLANTGROEP={'STRATEGIC ACCOUNT !!'}>} ,RELATIE_ID)

Formula guidebook or help

googel84 — Thu, 09 Dec 2010 15:37:23 GMT

I must say I'm not very sure because I've never tried before a set analysis similar to the one you are trying to interpret.

From my point of view you are counting distinct values of Opportunity ID that respect the following conditions:

Fiscal Year = 2011
Partner Geo = AMER
Program Name = ACE
First-in = 1
DateFlag = Submetted
the amount of Sold Value SRP greater than 50000

To find out more information on set analysis, please see page 1252 of QlikView Reference Manual. You might see also details on aggr function at page 1251.

Hope it helps.

Formula guidebook or help

martinpohl — Thu, 09 Dec 2010 16:28:14 GMT

Hello Wouter,

you need to move the distinct behind }>} and delete the comma.

count ({$<KLANTGROEP={'STRATEGIC ACCOUNT !!'}>}distinct RELATIE_ID)

Regards

Formula guidebook or help

Mon, 13 Dec 2010 09:25:15 GMT

thank you all for the help, it worked!!!

Wouter