topic Re: Counting dimension in expression in QlikView

Counting dimension in expression

arjendewolf — Mon, 12 Feb 2018 15:44:13 GMT

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

Re: Counting dimension in expression

Anil_Babu_Samineni — Mon, 12 Feb 2018 16:47:53 GMT

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)

Re: Counting dimension in expression

sunny_talwar — Mon, 12 Feb 2018 17:35:54 GMT

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

Re: Counting dimension in expression

arjendewolf — Tue, 13 Feb 2018 13:41:05 GMT

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!

Re: Counting dimension in expression

sunny_talwar — Tue, 13 Feb 2018 17:13:39 GMT

May be this

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