topic Re: Previous months value not getting correctly in QlikView

Previous months value not getting correctly

ashis — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

In my data model, I have a field in my master calendar call Month Counter , it is define as

Year(Date) * 12 + num(Month(Date)) as [Month Counter] (combination of Year and Month)

I have created variable PreviousMonth as

=max([Month Counter]) -1

Now if I want to get average of xxx of the previous month I put it in the expression as

=avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx)

This is giving me wrong result , if I write the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx) , I get null value.

Just to test my month counter I took two text boxes and put expression as

current month = =max([Month Counter])

previous month ==max([Month Counter]) -1 it gives correct output though.

Any idea where I am doing wrong.

Re: Previous months value not getting correctly

arjakumar — Thu, 29 Mar 2018 11:32:16 GMT

Hi Ashis,

Please provide the sample values.

Thanks

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 11:39:30 GMT

For let say if I select Mar 18 and current value is 130 then

Feb'18 as 128

the Previous month =avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx) expression is giving some different value than 128 which is incorrect.

Re: Previous months value not getting correctly

sunny_talwar — Thu, 29 Mar 2018 11:40:07 GMT

How about one of these?

=Avg({1<[Month Counter] = {'$(=$(vPreviousMonth))'}>} xxx)

or this

=Avg({1<[Month Counter] = {'$(=vPreviousMonth)'}>} xxx)

Re: Previous months value not getting correctly

arjakumar — Thu, 29 Mar 2018 11:50:35 GMT

Please take out the single quotes ('') and try

=Avg({1<[Month Counter] = {$(=vPreviousMonth)}>} xxx)

Re: Previous months value not getting correctly

sunny_talwar — Thu, 29 Mar 2018 11:56:08 GMT

Why do single quotes need to be taken out?

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 11:58:08 GMT

Hi Sunny,

It is same .

For current month Avg I am writing as

current month= avg({<[Month Counter]={'$(=MAX([Month Counter]))'}>} xxx)

for previous month I am writing as

Previous month =avg({1<[Month Counter]={'$(vPreviousMonth)'}>} xxx)

now when I choose current month as march it gives me current value 126.3933 and for FEB 126.76706

But when I choose month as feb the current value is 125.9241 instead of 126.76706

In this case the data is not matching .

Re: Previous months value not getting correctly

Chanty4u — Thu, 29 Mar 2018 11:59:56 GMT

try this

=Avg({1<[Month Counter] = {"=$(=$(vPreviousMonth))"}>} xxx)

Re: Previous months value not getting correctly

sunny_talwar — Thu, 29 Mar 2018 12:00:38 GMT

Is there a sample you would be able to share to look at this issue?

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 12:01:17 GMT

Same result.

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 12:06:55 GMT

I tried but same result.

Re: Previous months value not getting correctly

sasiparupudi1 — Thu, 29 Mar 2018 14:29:52 GMT

What value you get if you put '$(vPreviousMonth)'in a text box?

Re: Previous months value not getting correctly

sasiparupudi1 — Thu, 29 Mar 2018 14:30:52 GMT

you could try

avg({<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx)

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 15:13:01 GMT

Hi Sasidhar,

If I use the following syntax

avg({<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx)

I get null value. however I was using same syntax only difference is

avg({1<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx) , then it gives me value. But this value is not correct .

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 16:02:23 GMT

Sunny,

My current month value I compared with excel it is correct but the previous month calculation is not correct.

Is the formula wrong?

Re: Previous months value not getting correctly

ashis — Thu, 29 Mar 2018 16:03:32 GMT

I get numerical value. As it is combination of year and month.

Re: Previous months value not getting correctly

sunny_talwar — Thu, 29 Mar 2018 16:03:38 GMT

Don't think so... but I don't really know what you have

Re: Previous months value not getting correctly

boorgura — Thu, 29 Mar 2018 17:06:37 GMT

What other selections do you have? Should you be using "1" in the set analysis?

If you have any other selections, other than the date - then Current Month and Prev month expressions are looking at different population.

If the selection of date/month is driven by a different field - may just ignoring that selection in the set expression is a better way to do.

Re: Previous months value not getting correctly

ashis — Sun, 01 Apr 2018 16:51:15 GMT

So than how do I calculate previous month . I do have another selection criteria in the filter.

I need to calculate previous month as well as previous to previous month.

Could you please suggest what should I use in the set analysis .

Re: Previous months value not getting correctly

ashis — Mon, 02 Apr 2018 05:24:06 GMT

Hi Sunny,

I understand why I am getting wrong value for previous month.

Because I am using this 1 in the set analysis so it is considering all the record set instead of my current selection.

=avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx)

current month = =max([Month Counter])

previous month ==max([Month Counter]) -1 it gives correct output though.

However If I remove 1 from the set analysis and modify the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx) , then I get same value as I get for current month that is

=avg({<[Month Counter]={'=max([Month Counter]) '}>} ReportTimeInSeconds)

Do you have any idea why it is giving the same value or anything i am missing here.