Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

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
sunny_talwar

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
Kushal_Chawda

try this

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

kunkumnaveen
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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

kunkumnaveen
Specialist
Specialist
Author

Any suggestions plz

vishsaggi
Champion III
Champion III

Not sure may be this?

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

Kushal_Chawda

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

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

Anonymous
Not applicable

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

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

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