Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kunkumnaveen
Valued Contributor

how to write expression

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

Untitled.png

1 Solution

Accepted Solutions

Re: how to write expression

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))

View solution in original post

12 Replies

Re: how to write expression

try this

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

kunkumnaveen
Valued Contributor

Re: how to write expression

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)

kunkumnaveen
Valued Contributor

Re: how to write expression

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

kunkumnaveen
Valued Contributor

Re: how to write expression

Any suggestions plz

vishsaggi
Esteemed Contributor III

Re: how to write expression

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

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

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

machghoulhaydhe
New Contributor

Re: how to write expression

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 : ...

mattquinnterex
Contributor II

Re: how to write expression

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?

machghoulhaydhe
New Contributor

Re: how to write expression

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

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