topic Re: how to write expression in QlikView

how to write expression

kunkumnaveen — Thu, 11 May 2017 11:54:38 GMT

Hello,

how to get count of common customers who bought product in both Finance and AD in the same year

i wrote a expression for common customers who bought product in Finance and AD irrespective of year,

i mean if some customer bought a finance product in 2014 and same customer bought a AD in 2016 that will make my count as 1

which i am able to achieve by below expression

Count(distinct{<MGCID =p({<Group ={'AD'}>})* p({<Group = {'Finance'}>})>}MGCID) (working good)

Now i want count of customers who bought in same year

i mean if some customer bought a finance product in 2014 and same customer has to buy a AD product in 2014 then my count is 1

i have Year field

Re: how to write expression

Kushal_Chawda — Thu, 11 May 2017 12:06:00 GMT

try this

Count(distinct{<MGCID =p({<Group ={'AD'},Year={"$(=max(Year))"}>})* p({<Group = {'Finance'},Year={"$(=max(Year))>})>}MGCID)

Re: how to write expression

kunkumnaveen — Thu, 11 May 2017 12:17:34 GMT

i want to display all the three year not only a max year.......

Count(distinct{<MGCID =p({<Group ={'AD'},Year={"$(=max(Year))"}>})* p({<Group = {'Finance'},Year={"$(=max(Year))>})>}MGCID)

Re: how to write expression

kunkumnaveen — Thu, 11 May 2017 12:21:49 GMT

instead of max function i tried Only function but its not working

Re: how to write expression

kunkumnaveen — Thu, 11 May 2017 12:57:12 GMT

Any suggestions plz

Re: how to write expression

vishsaggi — Thu, 11 May 2017 13:01:34 GMT

Not sure may be this?

= Count(distinct{<MGCID =p({<Group ={'AD'},Year={"$(>=Min(Year))"}>})* p({<Group = {'Finance'},Year={"$(=max(Year))>})>}MGCID)

Re: how to write expression

Kushal_Chawda — Fri, 12 May 2017 07:03:11 GMT

if you select the particular year, don't you think, below expression should work?

Count(distinct{<MGCID =p({<Group ={'AD'}>})* p({<Group = {'Finance'}>})>}MGCID)

Re: how to write expression

machghoulhaydhe — Fri, 12 May 2017 07:44:52 GMT

Bonjour,

Je pense que tu peux utilises la fonction AGGR.

Tu devrais plutôt l'écrire comme ceci:

Count( Distinct Total <MGCID > Aggr(tonExpression, Dimensions, autresDimensions)

Par "tonExpression", je pense à ta grande expression qui calcule le retard (je crois)

Comme dimensions, il faut mettre MGCID , plus les autres dimensions (séparées par des virgules ) qui permettent d'identifier une valeur unique pour les champs utilisé dans ton expression : ...

Re: how to write expression

Anonymous — Fri, 12 May 2017 08:41:39 GMT

How are you filtering by year? Do you have a master calendar or a time dimension table? Or are you just using the date from your data set to filter on?

Re: how to write expression

machghoulhaydhe — Fri, 12 May 2017 15:08:44 GMT

i have master calender and if i have any filtering by year i use

{<Calendrier.year={$(=Year(Max(Date_id)))}>}

Re: how to write expression

vishsaggi — Fri, 12 May 2017 15:18:09 GMT

May be try this?

= Count(distinct{<MGCID =p({<Group ={'AD'},Year={" >= Year(Date(Min(Date_Id))) "}>})* p({<Group = {'Finance'},Year={"$(=max(Year))>})>}MGCID)

Re: how to write expression

sunny_talwar — Fri, 12 May 2017 18:17:26 GMT

May be create a new field in the script using

LOAD ...

AutoNumber(MGCID & Year) as Key

and then try this

Count(DISTINCT {<Key = p({<Group ={'AD'}>}) * p({<Group = {'Finance'}>})>} MGCID)

or something like this

=Sum(Aggr(If(Count({<Group = {'AD'}>} MGCID) > 0 and Count({<Group = {'Finance'}>} MGCID) > 0, 1, 0), MGCID, Year))

Re: how to write expression

kunkumnaveen — Tue, 23 May 2017 09:18:55 GMT

Thanks Sunny