topic Re: LINEST_M(TREND LINE) Functionality and how it works my issue in QlikView

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

Anonymous — Fri, 27 May 2016 03:41:59 GMT

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

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

swuehl — Fri, 27 May 2016 09:48:25 GMT

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.

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

Anonymous — Fri, 27 May 2016 18:43:07 GMT

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

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

swuehl — Fri, 27 May 2016 19:06:26 GMT

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.

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

Anonymous — Sat, 28 May 2016 04:27:20 GMT

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

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

swuehl — Sat, 28 May 2016 09:59:46 GMT

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.

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

Anonymous — Sat, 28 May 2016 10:12:27 GMT

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,

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

swuehl — Sat, 28 May 2016 10:17:28 GMT

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.

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

Anonymous — Sat, 28 May 2016 10:43:51 GMT

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

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

swuehl — Sat, 28 May 2016 10:49:08 GMT

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"}>}

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

Anonymous — Sat, 28 May 2016 11:05:25 GMT

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

Regards,

Bob

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

swuehl — Sat, 28 May 2016 11:14:16 GMT

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

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

Anonymous — Sat, 28 May 2016 13:29:20 GMT

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

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

swuehl — Sat, 28 May 2016 13:44:31 GMT

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).

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

swuehl — Sat, 28 May 2016 13:46:42 GMT

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

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

Anonymous — Sat, 28 May 2016 13:54:12 GMT

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])

)

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

swuehl — Sat, 28 May 2016 14:36:00 GMT

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

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

Anonymous — Sat, 28 May 2016 16:02:56 GMT

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,