LINEST_M(TREND LINE) Functionality and how it works my issue

[Anonymous]

Hi Team,

Good Day

yeah, i am struggling one senioriyo in my dashboard.

before that i read max of questions and answers regarding LINEST_M  Functionality but coming to my issue i am not able to find solution

issue:

let us i have3 fields in table .fields are date and peoplecountweekly ,peoplecountevery15days

peoplecountweekly:

it showing  count of people for every weekly

peoplecountevery15days:

it showing count of people for every 15 days

using this date function, peoplecountweekly,peoplecountevery15days i want to create trend line(Linest m )

peoplecountweekly: it is working fine as i expected

FOR ABOVE THING I WAS GETTING EXACT LINEST_M FUNTION VALUE

LINEST_M(aggr($(peoplecountweekly),[Date]),aggr([Date],[Date])--IT MATCHING MY REQUIREMENT AS WELL

-------------

peoplecountevery15days:i am facing issue on bellow one

FOR ABOVE THING I WAS GETTING WRONG LINEST_M FUNTION VALUE

LINEST_M(aggr($(peoplecountevery15days),[Date]),aggr([Date],[Date])-

HERE I WANT TO KNOW HOW IT WORKS FOR peoplecountevery15days

-->IN ABOVE  ZERO VALUES OF DATES ALSO TAKING FOR CALCULATION OF LINEST_M

-->IN THAT CASE I WANT TO RESTRICT THE ZERO VALUE DATES IN LINEST_M CALCULATION

-->HOW CAN I DO?

Please suggest me Team for best approach

Regards,

b B

[Anonymous]

if you dont mind could you please post sample QVW file with your idea"s

Regards,

Bob

[swuehl]

Well, I am still unsure what you want to achieve, attached a QVW with the suggested set expression

[Anonymous]

Hi swuehl,

hay dont confuse

please see my QVW dashboard.

in that just we need to one thing i need correct  Linest_m function for "TotalPeopleAvailablefor15days"

that my main aim

i tired your new suggested code as well it showing error please look on Attached QVW file

regards,

Bob

[swuehl]

This is basically the same request you posted initially, correct?

Well, the answer would be, to use a filter, e.g. set analysis in the linest_m function,

but since you don't want to change your variables nor the fields, I am not sure how you expect this to work.

You can't just have everything put into a single expression / variable, if you want to apply filters to that expression (without using selections in your data model that will affect all aggregations in that state).

[swuehl]

Maybe consider using a CROSSTABLE LOAD to transform your table in the data model to a straight table.

I think this should make your expressions easier.

The Crosstable Load

[Anonymous]

yeah,now  made  variables  accordingly to  my original requirement so now can you reply with your ideas accordingly then i will make that changes in  my dashboard as well

can you correct bellow script accordingly with my new QVW file:

=LINEST_M({< Date = {"=round(sum(peoplecountevery15days)*100,0.1) >0"}>}

aggr(Sum({< Date = {"=round(sum(peoplecountevery15days)*100,0.1) >0"}>} peoplecountevery15days),[Date]),

aggr(Only({< Date = {"=round(sum(peoplecountevery15days)*100,0.1) >0"}>}[Date]),[Date])

)

[swuehl · Accepted solution]

This is my last reply to this thread, I am little bit tired of doing mostly the same thing again and again...

[Anonymous]

Thanks a  Lot 

swuehl

you Gave best result to me and it works Very good

mainly thanks for your patience.to solving this issue i fell almost you sit beside to me

Regards,

Bb