Counting dimension in expression

[arjendewolf]

Hi all,

I am trying to find a way to show the number of customers that went up and down in turnover per country (or other dimension) vs same period last year. I have two charts, one for turnover per countries which went up and another one for countries which went down in turnover.

This is the expression I use for displaying the turnover for countries that have increased in turnover vs same period last year ([Netto vk] is the sales amount):

if(sum({$ <Year={$(=max(Year))}, mydate={"<=$(=max(mydate))"}>} [Netto vk]) > sum({$ <Year={$(=max(Year)-1)},mydate={"<=$(=num(setdateyear(max(mydate),max(Year)-1)))"}>} [Netto vk]),

sum({$ <Year={$(=max(Year))}, mydate={"<=$(=max(mydate))"}>} [Netto vk])

)

Instead of showing the turnover, I’d like to show the number of customers for that country that have increased and the number of customers that have decreased in turnover. Any thoughts on how I could solve this?

I have made a flag (cuscounter) to aggregate.

Many thanks for your help!

Arjen

[Anil_Babu_Samineni]

May be this?

Count(

if(sum({$ <Year={$(=max(Year))}, mydate={"<=$(=max(mydate))"}>} [Netto vk]) > sum({$ <Year={$(=max(Year)-1)},mydate={"<=$(=num(setdateyear(max(mydate),max(Year)-1)))"}>} [Netto vk]),

sum({$ <Year={$(=max(Year))}, mydate={"<=$(=max(mydate))"}>} [Netto vk])

), Customer)

[sunny_talwar]

Or this

If(Sum({$ <Year = {$(=max(Year))}, mydate = {"<=$(=max(mydate))"}>} [Netto vk]) > Sum({$ <Year={$(=max(Year)-1)}, mydate = {"<=$(=num(setdateyear(max(mydate),max(Year)-1)))"}>} [Netto vk]), Count(DISTINCT {$<Year={$(=max(Year))}, mydate={"<=$(=max(mydate))"}>} Customers))

[arjendewolf]

Thank you for your help so far! Unfortunately, this does not work, since the counting of the customers per country only happens if they have increased in turnover. I think I will have to use the P in set analysis. So i tried the following, but i'm not able to get the syntax right.

This expression works: sum({$ <code={">1"}, cusno = p({<sales= {">0"}>} cusno)>} cuscounter)

But when I try to add 'sum' to the set analysis, the expressions fails:

sum({$ <code={">1"}, cusno = p({<sum(sales)= {">0"}>} cusno)>} cuscounter)

Any thoughts on this?

Thanks again!

[sunny_talwar]

May be this

Sum({$<code={">1"}, cusno = {"=Sum(sales) > 0"}>} cuscounter)