Calculating avg of previous day regarding the user selection

[#1]

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 

[sunny_talwar]

May be this:

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

[#3]

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?

[#5 · Accepted solution]

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)