topic DATE/TIME IN SET ANALYSIS (NEWBIE QUESTION) in QlikView

DATE/TIME IN SET ANALYSIS (NEWBIE QUESTION)

provie98 — Fri, 18 Mar 2011 20:05:24 GMT

Hey all, I'm new to Qlikview. I'm not a programmer and at all. I've take the developer I and developer II courses but I always get stuck on set analysis.

I'm trying to do a year to year comparision. I have a beginning date called PBEGDATE that is actually a date and a time with seconds. I'm trying to compare year over year. I have the following three variables that I got out of the Developer II book.

For the Current Year:

Label:

Only(Date_Added_Year)

Formula:

sum

({$<Date_Added_Year={$(=Only(Date_Added_Year))}>} PROVISION_LOAN_LOSS_EXPENSE)

For the Previous Year:

Label:

Only(Date_Added_Year)-1

Formula:

sum

({$<Date_Added_Year={$(=Only(Date_Added_Year)-1)}>} PROVISION_LOAN_LOSS_EXPENSE)

For the Year to Year comparison:

Label

Only(Date_Added_Year) & ' vs ' & (Only(Date_Added_Year)-1)

Formula:

(

sum({$<Date_Added_Year={$(=Only(Date_Added_Year))}>} PROVISION_LOAN_LOSS_EXPENSE)) -

(

sum({$<Date_Added_Year={$(=Only(Date_Added_Year)-1)}>} PROVISION_LOAN_LOSS_EXPENSE))

My question is based on my formulas, do you have to format or change my original date to show only a date and not include the time? If so, how do I go about doing that?

Thanks for all your help. I'm hoping a year or two, I won't be a newbie at this. 🙂

My question is based on my formulas, do you have to format or change my original date to show only a date and not include the time? If so, how do I go about doing that?

Thanks for all your help. I'm hoping a year or two, I won't be a newbie at this. 🙂

DATE/TIME IN SET ANALYSIS (NEWBIE QUESTION)

Oleg_Troyansky — Fri, 18 Mar 2011 20:11:22 GMT

Hi, welcome to QlikView and to QlikCommunity!

If you used the actual Dates in your Set Analsys, then - yes, you'd have to format the dates in a very specific way to make the Set Analysis condition work. However, since you are using Years (if that's truly the case), there shouldn't be any need in any reformatting.

In order to be sure, create a List Box and show the contents of the field "Date_Added_Year" - do you see years or timestamps?

DATE/TIME IN SET ANALYSIS (NEWBIE QUESTION)

provie98 — Fri, 18 Mar 2011 20:16:15 GMT

I'm such a newbie that I realized that my set analysis is working just fine. I had a list box with another variable on it called period_year. I was using that. I couldn't figure out why my set analysis wasn't working until you had me make a list box with date_added_year. When I clicked on a field in the date_added_year it started working. Duh! It makes sense because how's it gonna know if you don't click on it?

Thanks again! I've spent an hour on this and it was something stupid. LOL! Have a good day!

DATE/TIME IN SET ANALYSIS (NEWBIE QUESTION)

Oleg_Troyansky — Fri, 18 Mar 2011 20:19:29 GMT

Ah, yes, - the problem was in the use of function only() - it will ONLY return a value when ONLY one value is available...

From this perspective, max() could work better 🙂