current day vs previous day

[#1]

Hello,

Im having some trouble by getting my previous day.

Im using this formular

My Current day is

=sum({<CD>} BuyPriceRealized)

//CD is equal to

inday(TempDate, $(vToday), 0) * -1 AS CD,

from my mastercalendar

//This one returns the current day i have selected (I dont wont to show today, cause i dont have data atm) so i want to hardcorded previous day, and im trying to do it by this:

=sum({$<Year={"2012"}, Month={"sep"}, Day{"24"}>} BuyPriceRealized) //I does not show anything, but if i remove day i get the sum from 2012 and sep.. So it seems to be my Day there is a problem with, but i cant figure out what...

Can anyone help??

[johnw · Accepted solution]

Assuming you cut and pasted the expression and it isn't a typo in the post, you missed the equals sign after Day.

I also recommend using single quotes for literals.  Double quotes specify a search expression.  Now, searching for "2012" will just return '2012', so it's the equivalent of '2012', but I believe it's an extra (albeit trivial) step for QlikView to go through.

[#3]

Haha, Jesus christ - I should go home!!!! It was the equal sign - How dumb can i be Sorry about that.....I really couldnt understand that i was working cause it seemed kinda right

[#4]

1st of all i see you have not equated Day={24}...try like this if it helps

=sum({$<Year={2012}, Month={"sep"}, Day={24}>} BuyPriceRealized)

I have another solution for you

sum({$<Date={'=$(=today()-1)'}>} BuyPriceRealized)

where Date field is from the master calendar

[johnw]

I agree with hsharmas's alternative solution, assuming you have a master calendar and it has a date in it.  And you should have a master calendar, and it should have a date in it.  That's going to be much easier than calculating the right year, month and day.

[#6]

Ive solved it - it was because of the = sign.. And the other thing does not work, cause it takes from our real life date (so it would be the 16 it would take)

But thanks

[#7]

I have a mastercalendar with all my dates - But my enddate (where theres data till) is the 24th of september. And if i use today i will get the system date (17th of october) - Therefore i need to hardcode it -

[johnw]

Then something like this?

sum({<Date={'$(=date(max(Date)-1))'}>} BuyPriceRealized)

sum({<Date={'$(=date(enddate-1))'}>} BuyPriceRealized)

I suppose if your end date will never change, you could hardcode it, but assuming you keep loading in new data over time, you'd want to handle it with an expression.

And I realize we're trying to solve a "problem" that isn't really a problem and that you didn't even ask about.

[#9]

I absolutely agree with you - It should not be hardcoded at all - but in this case it will never change, and data will never ever come in again, so its easier to hardcode it. But i totally agree with you it should be hardcoded so it whould automatically change every day (It is an KPI) so if it was not a POC it whould be prefered alot

sum({$<Date={'=$(=today()-1)'}>} BuyPriceRealized) <-- this code only returns my current selected day, why i dont know, cause it should be able to take the day before even though i have selected 24th of september??

[johnw]

I don't actually understand why it would return your selected date.  I wouldn't expect it to work exactly as is, though, even if the general idea is correct.

• You'll probably need to wrap the today()-1 in a date() function, or it will forget it's a date and just return a number.  Since set analysis does a text comparison rather than a date comparison, this formatting problem would keep it from having a match.
• You don't have any data for October 16, 2012, right?
• Your calendar provides other fields for specifying a date without actually selecting a Date.  When you do the Date=, you're only overriding the date selection.  To really make it work, you would need to also tell QlikView to ignore the selections in other fields in the calendar.  This would normally be done as a hardcoded list of fields:

sum({<Date={'$(=date(today()-1))'},Year=,Quarter=,Month=,Week=,Day=>} BuyPriceRealized)

But it CAN be done without the list of fields by telling QlikView to ignore every field in the calendar except for the Date.  The syntax is nearly impossible for a person to parse, though, so I'd have to look it up or reinvent it, and chances are you don't want it anyway.  Just letting you know it's possible if your calendar has 20 different fields on it, and you're adding new ones all the time, and don't want to have to maintain your expressions.