Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating avg of previous day regarding the user selection

Hello Everyone,

I'm new to qlikview and I'm having some issues to calculate the value of the previous day regarding the selction made  by the user.

I already calculated the value for the month using this expression:

Created a new variable MesAnterior = =Monthname(AddMonths(MakeDate(GetFieldSelections(Data.Ano),Month(Date(Date#(GetFieldSelections(Data.Mes),'MMM')))), -1))

and in the expression used =avg({1< Data.AnoMes ={'$(MesAnterior)'}>} DiscoDisponivel)

It works fine. For example if the user select the month of October it gives the average of the month of Setember.

Now I want to do this but with the days.

If the user select the 5 of October it should give the average of the 4 of October.

So far I create the variable DiaAnterior =Date(MakeDate((Data.Ano),(Data.Mes)),Data.Dia-1) and in the expression used =avg({1< Data.AnoMes ={'$(DiaAnterior)'}>} DiscoDisponivel) .

It returned - .

Can you help me ?

Best Regards,

Joana Oliveira 

1 Solution

Accepted Solutions
Not applicable
Author

I can't share the qvd because of confidential data, but I can tell you whats happening.

When I use in the text box = Date(Max(Data.AnoMes) - 1, 'DateFieldFormatHere')

The numbers don't change regarding  my day selection.

If I change my expression to =Date(Max(Data.Dia) - 1, 'DD')

It doesn't go to the previous day but goes to two previous days.

It worked when I used the expression DiaAnterior= =Date((Data.Dia) , 'DD') and then it calculated the correct average(day before) regarding my day selection using the expression =avg({1< Data.Dia ={'$(DiaAnterior)'}>} DiscoDisponivel)

Thank you for your help.

Best Regards,

Joana Oliveira

View solution in original post

6 Replies
sunny_talwar

May be this:

DiaAnterior = Date(Max(Data.AnoMes) - 1, 'DateFieldFormatHere')

Not applicable
Author

Thank you for your answer.

Unfortunately it didn't work.

Any more suggestions?

Best Regards,

Joana Oliveira

sunny_talwar

Would you be able to share a sample where this isn't working?

Not applicable
Author

I can't share the qvd because of confidential data, but I can tell you whats happening.

When I use in the text box = Date(Max(Data.AnoMes) - 1, 'DateFieldFormatHere')

The numbers don't change regarding  my day selection.

If I change my expression to =Date(Max(Data.Dia) - 1, 'DD')

It doesn't go to the previous day but goes to two previous days.

It worked when I used the expression DiaAnterior= =Date((Data.Dia) , 'DD') and then it calculated the correct average(day before) regarding my day selection using the expression =avg({1< Data.Dia ={'$(DiaAnterior)'}>} DiscoDisponivel)

Thank you for your help.

Best Regards,

Joana Oliveira

sunny_talwar

So the issue is resolved then? If it is, I would suggest marking your response as correct and close this thread.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Anil_Babu_Samineni

Try This


DiaAnterior= =Date(Data.Dia -1 , 'DD')


=avg({1< Data.Dia ={'$(DiaAnterior)'}>} DiscoDisponivel)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful