topic Re: Date format question in QlikView

Date format question

Fri, 03 Aug 2012 08:57:19 GMT

Hello everyone,

I have a quick question. I don't know if it's exactly possible, but can I show a value in QV throughout the month if in the Excel file the value is set to a specific day?

In the Excel file, near the target for January I have the date 01.01.2010, but the target should apply for the whole month. If in QV I select January 02 it doesn't show me anything, and that's normal. What I would like to know is if I can set the value to the month of January according to only the month and year of the date in Excel and disregard the day. Is it something that I can do in the load script or is it a function I can use later on? I have the same problem trying to do this for a year period. Target is set to 01.01.2010 and even though it applies for the whole year, if I click on February it's not shown anymore.

For this project I can also modify the value in the Excel file. How should I list the date there so that QV can get the info accordingly?

Does it make any sense?

Bogdan

Re: Date format question

Anonymous — Fri, 03 Aug 2012 09:21:15 GMT

as i understand your question...

You can repeat the same target (amount/quantity) for the entire month in the load script itself.. and by doing that if you select 2nd Jan or 3rd Jan.. the target would show up normally..

hope i am in sync with your query..

Re: Date format question

Fri, 03 Aug 2012 11:00:18 GMT

Hello Aadil,

Thank you for your response. I think what you suggested might do the trick, but do you know how to to that?

Thank you,

Bogdan

Re: Date format question

Fri, 03 Aug 2012 11:03:44 GMT

Can u share some sample ?

Re: Date format question

Fri, 03 Aug 2012 11:37:57 GMT

Sure! This is how the table in the load looks like:

Concatenate LOAD [Cod Magazin],

Data,

Target

FROM

Targeturi.xlsx

(ooxml, embedded labels, table is Sheet1);

While the Excel table looks like this:

Cod Magazin	Data	Target
X1	01.01.2010	1600000
X1	01.01.2011	1700000
X2	01.01.2010	1700000
X2	01.01.2011	1800000
X3	01.01.2010	1900000
X3	01.01.2011	2000000
X4	01.01.2010	1800000
X4	01.01.2011	1900000
X5	01.01.2010	2000000
X5	01.01.2011	2100000

Does this help?

Re: Date format question

Fri, 03 Aug 2012 11:43:25 GMT

This is the one where I would like the entire year to show the target specified in the table. The one related to the month and daily situation is stil being worked upon, so it's not yet ready

Re: Date format question

Fri, 03 Aug 2012 11:46:28 GMT

Hi there,

I assume you have a calendar table in you data cloud?

If so, you could create a field 'Monthstart' in that table for each date. (use the function monthstart).

Then load the excel as following:

Tagrtes:

load

monthstart(Data) as MonthStart,

[Cod Magazin],

Target

FROM

Targeturi.xslx

(...)

This will have as a consequence that yout targets will not be in your fact table. so keep that in mind. It might give you trouble if the [Cod Magazin] is a field that also exists in other tables.

(to do this for entire years, use YearStart)

Kind regards,

Yves

Re: Date format question

Fri, 03 Aug 2012 12:18:22 GMT

Hello Yves,

I do have a calendar in the document. Unfortunatelly the field [Cod Magazin] is a key that is located in other tables too. I tried loading the Yearstart as you mentioned, but doing so, the results are no longer linked to the Calendar in my document.

Here is how my Calendar looks like and it seems that I already have MonthStart

Let varMinDate = Num(Makedate(2010,1,1));
Let varMaxDate = Num(Makedate(Year(today())+1,12,31));
Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS Datefield
AUTOGENERATE (1) 
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);
Calender:
LOAD 
Datefield AS Data,
Year(Datefield) AS Year,
Month(Datefield) as Month,
Dual(Date((Datefield),'MMMM'),Num(Month(Datefield))) as Months,
Floor(Monthstart(Datefield)) as Monthstart,
Week(Datefield) AS Week,
Weekday(Datefield) AS Weekday,
Day(Datefield) AS Day,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter, 
Monthname(Datefield) AS RollMonth,
Num(Monthname(Datefield)) AS NumRollmontj,
(Num(Floor(Monthend(Datefield)))-Num(Floor(Monthstart(Datefield)))+1) as NumberDays,
If(Num(Weekday(Datefield))<5,1,0) as WorkingDay,
YearToDate(Datefield,0) as YTDActualYear,
YearToDate(Datefield,-1) as YTD1PreYear,
YearToDate(Datefield,-2) as YTD2PreYear,
YearToDate(Datefield,1) as YTDFollowingYear
RESIDENT Datefield;
//Delete temp table
DROP TABLE Datefield;
//Clean Up Variables
SET varMinDate = ;
SET varMaxDate = ;

Please have in mind that I'm new to this software and I appreciate your patience with me in case I don't understand everything.

Thank you,

Bogdan

Re: Date format question

Fri, 03 Aug 2012 12:46:02 GMT

Hi,

if [Cod Magazin] is a key to other tables, the solution I suggested might not be the one you want.

But I think a good sollution just popped up in my mind.

what expression do you use to display the Target?

sum(Target) ?

You could use set analysis to disrecard the selections in the day field.

for example:

sum({$<Day=>} Target)

Regards,

Yves

Re: Date format question

Fri, 03 Aug 2012 13:27:40 GMT

Hello Yves,

Great idea! It worked for the Targets table. I used it like this sum({$<Month=>} Target) and now I can select any month of that particular year and it works as designed .

A simple solution to a big issue.

Yves, thank you very much for your help.

Bogdan

Re: Date format question

Fri, 03 Aug 2012 13:56:53 GMT

No problem.

I read that you were still working on the table with the monthly targets.

If you add them, and the field is also called target, I have to be carefull not to make a total of the yearly and the monthly target.

But I think that a yearly target should be the sum of the monthly targets, so if you ahve the monthly gigures, you could leave the yearly targets out of the excel.

regards,

and good luck and fun with qlikview in the future.

Yves

Re: Date format question

Fri, 03 Aug 2012 14:15:05 GMT

Thanks for the tip Indeed it's easier to set monthly targets and just tell QV to add them afterwards. In case i run into anymore trouble that I cannot solve on my own, you'll find my questions here

Thank you again,

Bogdan