Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
as below
Count({<customer={"sum({<year=>}sales)>5000"}>} Distinct customer)
@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)
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
Hi @CHAY-92 ,
Maybe this:
Count( {< customer = {""=sum( {< year ={">=$(=Max(Year)-2)"} >} sales )>5000""} >} Distinct customer )
I hope it can helps.
Best Regards
Hi ,
I tried this formula but it always shows only selected year count not for 3 years
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
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)
Hi vinieme,
Thanks for your reply
I am using month year field.
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)