Date format question

[#1]

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

[Anonymous]

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..

[#3]

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

[#4]

Can u share some sample ?

[#5]

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?

[#6]

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

[#7]

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

[#8]

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

[#9 · Accepted solution]

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

[#10]

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