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

[swuehl]

You should be able to use set analysis to filter your count values:

LINEST_M({<[peoplecountevery15days] = {">0"}>} aggr($(peoplecountevery15days),[Date]),aggr([Date],[Date]) )

assuming that [peoplecountevery15days] is a field in your data model.

You might need to add the set analysis also to your aggr() aggregation functions (or implicte Only()).

For questions like these, it would be better if you post all e.g. variable definitions you are using, like $(peoplecountevery15days).

Best by posting a small sample QVW.

[Anonymous]

Hi swuehl,

Great .:)

for your understanding  i am adding QVW file .could you please looking on Attached QVW file

and 

LINEST_M({<[peoplecountevery15days] = {">0"}>} aggr($(peoplecountevery15days),[Date]),aggr([Date],[Date]) )

--the above code throughing error...please look on above code

Regards,

Bob

[swuehl]

Why are you using a dollar sign expansion to reference the field?

LINEST_M(aggr($(peoplecountweekly),[Date]),aggr([Date],[Date])

I think the expression could look like

=LINEST_M(

     {<[peoplecountevery15days] = {">0.0%"}>}

     aggr(Only({<[peoplecountevery15days] = {">0.0%"}>}[peoplecountevery15days]),[Date]),

     aggr(Only({<[peoplecountevery15days] = {">0.0%"}>}[Date]),[Date])

    )

This replicates your requested result with a selection spanning the first 4 dates.

[Anonymous]

Hi swuehl

Thnaks a Lot swuehl  

the way of your implemented for Code using fields it"s perfectly fine

but i instead of that field can i use variable?

Like bellow:

=LINEST_M(

     {<$(peoplecountevery15days) = {">0.0%"}>}

     aggr(Only({<$(peoplecountevery15days)= {">0.0%"}>}[peoplecountevery15days]),[Date]),

     aggr(Only({<$(peoplecountevery15days) = {">0.0%"}>}[Date]),[Date])

    )

if ii using above one it throwing error like "SET MODIFER EXRESSION"

Could you please look on the above issue and For your understand i am adding  QVD file file as well please look on that one

Thanks,

bob

[swuehl]

You should be able to use a variable in you set modifier, BUT only if it expands to a single field name, not to an expression.

Your variable is defined as

round(sum(peoplecountevery15days)*100,0.1)

hence the set expression will look like

{< round(sum(peoplecountevery15days)*100,0.1) = {">0.0%"}>}

There are only field names allowed on the left side of the set modifier equal sign, so this is NOT a valid set expression.

The limitation is due to the fact that selections are only possible on fields.

[Anonymous]

in my requirement i need to use variable in left side also.beze like this calculation i want to use in  20 variables.

if i use fields in left side i have to manage more things so that way of doing is not possible in my requirement

so is there any other process for to find solution

since so long time i was tied up this issue.but your solution is very helpful for me but i need some thing more...

if you know any other process please post to me with sample file  ..really its helpful to me

Thanks swuehl

Regards,

Bb

[swuehl]

If you want to aggregate your field values ((sum(peoplecountevery15days))

you need to clarify what should be the internal grouping entity here.

Is it Date field? Or something else?

Or do you just want to reformat your field values from a percentage to a number between 0 and 100?

Then do this in your script, creating a new field and use this field in your set expression.

[Anonymous]

yeah Date Field,

-->((sum(peoplecountevery15days)) value format we can change based upon our requirement

if i restrict date field

Like you said  particular dates i have values so if i create new Date column in that  can i restrict the Date field.

note:

here IF condition i dont need because if i use if condition it show else part as well so i dont want else part any where

can you give any other idea for this

[swuehl]

Not quite sure if I can follow...

If you want to restrict your Date field values in your set expression based on the aggregated count field, then use Date field in the field modifier:

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