Row Total shows wrong data

[CHAY-92]

Hi all,

I m very confused about pivot default row  total options. If I select a year I need to pull 3 years of data with sum(sales)>50000 and count of those customers.

Dim: customer 

Measure 1 : if(sum(<year=>sales)>50000,sum(<year=>sales))

Measure 2;

Sum(aggr(Count(customer),customer) 

But I am getting wrong numbers 🙃 😕 

Kindly guide me how I will correct my error

[vinieme12]

as below

Count({<customer={"sum({<year=>}sales)>5000"}>} Distinct customer)

[sidhiq91]

@CHAY-92  A slight modification to what @vinieme12  has mentioned above. Since it is for 3 years, see the expression below:

Count({<customer={"sum({<year= {"$(=Max(Year)-2)"}>}sales)>5000"}>} Distinct customer)

[CHAY-92]

Hi Sidhiq,

I tried this expression 

Count({<customer={"sum({<year= {">=$(=date(addmonths(year,-35),'dd.mm')))<=$(=date(addmonths(year,0),'dd.mm'))}>}sales)>5000"}>} Distinct customer)

But I am getting error. Could you pls help on this.Thanks

[agigliotti]

Hi  @CHAY-92 ,

Maybe this:

Count( {< customer = {""=sum( {< year ={">=$(=Max(Year)-2)"} >} sales )>5000""} >} Distinct customer )

I hope it can helps.

Best Regards

[CHAY-92]

Hi  ,

I tried this formula but it always shows only selected year count not for 3 years 

[CHAY-92]

Hi,

Also along with the COUNT looking for customers ,whose sales are greater than 5000 for last 3 years

Dimension:  customer 

Measure: sum({$<[Cusomer Name] = {"=Sum({$<[orderYear] = {"">=$(date(Addmonths(orderYear),-35),'DD.MM.YYYY')))<=(date(addmonths(orderYear,0),'DD.MM.YYYY'))""}>}sales) > 5000"}>} Sales)

But using this formula gives only sum for selected year  not for 3 years data.

Please help me to fix this error 🙏. Thanks

[vinieme12]

is orderYear  as  Datefield or a Yearfield ?

Assuming its a Datefield , try below expression

=sum({<

[Cusomer Name] = {"=Sum({<[orderYear] = {"">=$(=Date(Addmonths(orderYear,-35),'DD.MM.YYYY'))<=$(=Date(Max(orderYear),'DD.MM.YYYY'))""}>}sales) > 5000"}

,[orderYear] = {">=$(=Date(Addmonths(orderYear,-35),'DD.MM.YYYY'))<=$(=Date(Max(orderYear),'DD.MM.YYYY'))"}

>} sales)

Assuming its a  YEAR field , try below expression

=sum({<

[Cusomer Name] = {"=Sum({<[orderYear] = {"">=$(=Max(orderYear)-2)""}>}sales) > 5000"}

,[orderYear] = {">=$(=Max(orderYear)-2)"}

>} sales)

[CHAY-92]

Hi vinieme,

Thanks for your reply

I am using month year field.

[vinieme12 · Accepted solution]

ideally switch to a Date field, because MonthYear will only make things tedious 

If you don't have a Datefield in your dataset then derive a date field using the MonthYEar field

example in your load script

, MonthStart(Date#(monthyearfield,'YYYY-MM')) as Datefield

Replace YYYY-MM with the format of the monthyearfield

then use the below expression

=sum({<

[Cusomer Name] = {"=Sum({<[Datefield] = {"">=$(=Date(MonthStart(Max(Datefield),-35),'DD.MM.YYYY'))<=$(=Date(Max(Datefield),'DD.MM.YYYY'))""}>}sales) > 5000"}

,[orderYear] = {">=$(=Date(Addmonths(Max(Datefield),-35),'DD.MM.YYYY'))<=$(=Date(Max(Datefield),'DD.MM.YYYY'))"}

>} sales)