Help me about an epression make sense

[Anonymous]

There are  2 expression as follow :

A first expresion:

+Sum({<Quarter={"$(vPriorQuarter)"} , Month={"$(vPriorMonth)"} , WeekYear={"$(vPriorWeek)"} Date={"$(vPriorDate)"} >} Sale)

with the variable below:

-vPriorQuarter =If(PeriodOp='Quarter',Ceil(max(Month)/3)-1,'*')

-vPriorWeek =If(PeriodOp='Week',Week(Max(Date))-1,'*')

-vPriorMonth =If(PeriodOp='Month',month(addmonths(max(Date) ,- 1)),'*')

-vPriorDate ==If(PeriodOp='Date',Date(KeyDate-1,'DD/MM/YYYY'),'*')

A second expression:

+Sum({<Year=, Quarter = , Month = , WeekYear= , Date= {"$(=Date(Max(Date) -1 ))"}  >} Sale)

They get the previous data. But I check the data  and the first expression is right data, the second expression is just only  right with a date selection, and isnot perfect with  month, quarter, week selection

Can anyone please answer me the following question

Do the first or the second is the best ?? and another solution for the second expression is right with month, quarter, week selection ?

[Anonymous]

I think the first expression gives the prevoius period where period is chosen by PeriodOp... so it's correct if this is your aim.

The second one ignores selecions made on Year, Quarter, Month and Weekyear: the only selection that affects the result is the one on Date and the result is the sum of Sal of the only previous day.

Which kind of calculation you have to implement?

[Anonymous]

The problem with the second expression is here:

max(Date)

When you select year, qtr, etc. - you get that max date defined by these selections.  As I undestand your purpose correctly, change it a little:

max({1} Date)

So it will be always absolute max date regardless year or quarter or month or whatever selection.

[jagan]

Hi,

Check below link hope it helps you.

Set Analysis for certain Point in Time

Regards,

Jagan.

[Anonymous]

Thank you very much mov‌,

max({1} Date) return the wrong date. its not previous date which I expect Can you help me again ?

[Anonymous]

Thank you so much jagan‌,

I'll like to the expression display the result what I select Quarter, Month, Week or Date. The time selection will be chose many time option.

Ex: this expression: Sum({<Year=, Quarter=, Month=, Week=, Date={‘$(=Date(Today()-1))’}>} Sales ) just only display data 1 previous day. when i select the difference time option like week or Month, its show the wrong data.

Do you mind if you explain clearly for me?

[Anonymous]

I have to implement  that the data will show data when I select many selection.

Ex: When I select Week Option, the expression will be displayed the data of week, and when I change the selection as Date , Month, Quarter or Year.. it will be show the data of current selection which i select.

[jagan]

Hi,

I am not sure what you are trying to do.  By default Qlikview will do calculations based on the current selections, if you select Year it will show that year data, if you month it will show the data of that month, if you select week it will show the data of that week.  You don't need to do anything special it works as such.

Regards,

jagan.

[Anonymous]

This last version looks reasonable, except I'd use double quotes rather than single.

Date={"$(=Date(Today()-1))"}

f it doesn't work, you better upload an example.  Oh, and make sure that the "Date" field is in the date format.

[Anonymous]

Hi jagan‌,

I know that default Qlikview will do calculations based on the current selections. But my customers have requirement that an expression will be display previous data and it change when they select time option with week, quarter , month and date. The following photo will make you understand clealy:

and I want to optimize my expression as follow expression:

Sum({<Year=, Quarter = , Month = , WeekYear= , Date= {"$(=Date(Max(Date) -1 ))"}  >} Sale).

to replace

Sum({<Quarter={"$(vPriorQuarter)"} , Month={"$(vPriorMonth)"} , WeekYear={"$(vPriorWeek)"} Date={"$(vPriorDate)"} >} Sale)

Can you give your solution?