Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Share of sale, aggr over 2 different dimensions

Hi! I have a pivottable where I first have a dimension "Month of order" and then Country.

I want to show share of sales in next column for every different row.  I want the share of sales to summarize as below. How do I write that aggr-expression?


Looks like ;

Month of order           Net sales          Share of sales

January                        

Total                                5M                          50 % ,                           (50 % of the year-total is in january)

USA                                2 M                           40 % ,                           (40 % of january total is in US)

Canada                          1 M                          20 %

Mexico                          1 M                           20 %

Other                              1M...                      20 %

 

February

Total                                5M                        50 %

USA                                  1                            20 %

Canada                          1                           20 %

Mexico                        1                              20 %

Other                         2                                40 %

 

Mar...

Labels (1)
7 Replies
Channa
Valued Contributor III

Re: Share of sale, aggr over 2 different dimensions

sum(netsales)/sum(total <Month>,<Country> netsales)

 

try this

Channa
Partner
Partner

Re: Share of sale, aggr over 2 different dimensions

Hi. I tried that one but it didn´t work. Maybe you can find out what´s wrong in the syntax?

error.sos.PNG

Re: Share of sale, aggr over 2 different dimensions

May be this

If(Dimensionality() = 2,
  Sum([Net Sales Amount])/Sum(TOTAL <[Month of Order]> [Net Sales Amount]),
  Sum([Net Sales Amount])/Sum(TOTAL [Net Sales Amount])
)
Partner
Partner

Re: Share of sale, aggr over 2 different dimensions

Thanks! However I realized there is one more thing I need to take into account.

It shall only count the net sales amount for current year. I tried to put in my expression for current year.     This one: Sum({$<$(vL.CY)>}[Net Sales Amount])

Syntax says ok when i Put it in the first part of expression, like this:

If(Dimensionality() = 2,
Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <[Booking Month]> [Net Sales Amount]),
Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <CountryCustomer> [Net Sales Amount])
)

However, I need the rows to have the last part also sum total for just current year, I tried this but without success:

Sum({$<$(vL.CY)>}[Net Sales Amount])/Sum(TOTAL <CountryCustomer> ({$<$(vL.CY)>}[Net Sales Amount]))

Re: Share of sale, aggr over 2 different dimensions

Can you check if this works?

Sum({$<$(vL.CY)>}[Net Sales Amount])
/
Sum(TOTAL <CountryCustomer> {$<$(vL.CY)>}[Net Sales Amount])
Partner
Partner

Re: Share of sale, aggr over 2 different dimensions

It says ok but gives just null-values. At the bottom of the picture you also see how the vl.CY-variable look.  It worked to change to current year like that for the numerator-part of the expression but not for the denominator.. Then it just gives null-values.. Any idea?

error2.sos.PNG

Re: Share of sale, aggr over 2 different dimensions

Not sure I see any issue? Would you be able to share a sample to show the issue?