Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results 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)

Best Regards,

Joana Oliveira

6 Replies
MVP

May be this:

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

Not applicable
Author

Unfortunately it didn't work.

Any more suggestions?

Best Regards,

Joana Oliveira

MVP

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)

Best Regards,

Joana Oliveira

MVP

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

Try This

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Community Browser