show detailed information per month

[#1]

Hello friends

I'm building a report and I have the NEXT issue.

I have a date "Transaction date" used as a method to filter information. For example: "View all transactions in January," as shown below.

The only information I list when it was "stored" or where "there are changes" in one transaction day. What I need is to see the full performance of January a day, it does not matter if the number in column "AVG ON-HAND BALANCE" and "ANNUAL DEMAND repeat for every day that no changes have occurred. As shown below.

Thanks in advance !

Karen[:S]

jan-10	Onhand balance	Annual Demand	RMT
1	2 216	302016	136
2	2 216	302016	136
3	2 216	302016	136
4	2 216	302016	136
5	2 216	302016	136
6	2 216	302016	136
7	2 216	302016	136
8	2 216	302016	136
9	2 216	302016	136
10	2 216	302016	136
11	2 216	302016	136
12	2 216	302016	136
13	2 216	302016	136
14	2 216	302016	136
15	2 216	302016	136
16	2 216	302016	136
17	2 216	302016	136
18	2 216	302016	136
19	2 216	302016	136
20	2 216	302016	136
21	2 216	302016	136
22	2 216	302016	136
23	2 216	302016	136
24	2 216	302016	136
25	2 216	302016	136
26	2 216	302016	136
27	2 300	302016	131
28	2 300	302016	131
29	4 250	302016	71
30	4 250	302016	71
31	4 250	302016	71
Average	2 418	302016	125

I do not know if the solution is to create a date variable and see if the records are within the range month for example .... I think I'm a little confused! besides I am new to this forum and QlikView!

[#2]

Hi Karen,

not sure if I´m on the right track. See the example and let me know if your problem is likely the same.

See the script for details.

Good luck!

Rainer

[#3]

Hi Rainer, thanks a lot for you speedy answer !!!

I think your code is very close to the solution to or desire, but this confused me a part and I hope you can explain it to me.

1. I succeed in creating the calendar without any problem and the listing information for each day of each month.

My problem is with this part of your code:

//*** Create a calendar and join with the original table

//*** Now the dates are complete

Join (Temp_PreData)

Load

MNR,

Date($(vMinDatum) + Iterno() - 1) as Datum

Resident Temp_PreData

While IterNo() <= $(vAnzahl) ;

PreData:

Load distinct

' ' as dummy,

Datum,

//*** Find dates without a status where employee number is the same as before

if(IsNull(Status) and MNR=peek(MNR),peek(Status),Status) as Status,

MNR

resident Temp_PreData

Order By MNR, Datum;

Drop Table Temp_PreData;

You represent MNR in the temporary table of dates, but in my case I would have that data in a table in my database, the same Status.
So I have problems to do the join with a table that you named in the charge as "TempActual" has the following format:

Temp_PreData:

LOAD * INLINE [Year, Month, Day

2010,01,1

2010,01,1

2010,01,2

2010,01,3

2010,01,4

2010,01,5

2010,01,6 ;

Load

Min(Day) as MinDay,

Max(Day) as MaxDay

Resident Temp_PreData;

Let vMinDay = Peek('MinDay', 0, 'MinMaxDay');

Let vMaxDay = Peek('MaxDay', 0, 'MinMaxDay');

Let vAnzahl = $(vMaxDay) - $(vMinDay) +1;

TempActual:

LOAD

MTSTNO AS [Total on-hand balance], (FOR YOU STATUS)

MTTRDT AS [Transaction date]; (FOR YOU DATUM)

SELECT *

FROM $(DataBase).TABLE;

If you can give to me one hand with the join.... I am very grateful !

One Hug

Karen

[#4]

Hi Karen,

is it possible for you to upload a small qv example i can play around with?

It´s helpfull for me to have the same kind of data as you.

Thank you!

Rainer

[#5]

Hi Rainer !!! 🙂

I attach one file, I hope this can help you understand my problem.

" Helping you to help me"

Many greetings!

Karen

[#6]

Karin,

I´ll come back to you tomorrow.

Have a nice evening!

Rainer

[#7]

Hi Rainer,

Thankssssssssssssssss !!!

i here whaiting hehehe

Have nice evening also.

Karen

[#8]

Good morning Karen,

you wil find some comments in the example to follow the steps needed to solve your problem.

It´s as always one way to reach the goal.

Best wishes and have a wonderful weekend!

Rainer

[#9]

Dear Rainer, Good Morning

Your code works wonderfully !!!!. BUT, I now have a little problem with the configuration of the date, as I want, give the option select by year or by month or by day, so I like to "split" the date, since currently only and full board is listed in the format yearmonthday is not optimal if a user wants to select the entire month. You understand me?

I have tried to format the variable "Transaction_date" with the following code:

Year (Date#(Num(Transaction_date),'YYYYMMDD'))&Num(Month(Date#(Num(Transaction_date),'YYYYMMDD')),00) AS YearMonth,

Year(Date#(Num(Transaction_date),'YYYYMMDD')) as Year,

Month(Date#(Num(Transaction_date),'YYYYMMDD')) as Month,

Week(Date#(Num(Transaction_date),'YYYYMMDD')) as Week,

Day(Date#(Num(Transaction_date),'YYYYMMDD')) as Day,

WeekDay(Date#(Num(Transaction_date),'YYYYMMDD')) as WeekDay

But not responding.

Thank you very much for all the help you gave me these days!

A hug and a very good start this week for you.

Karen

[#10]

Dear Rainer, Good Morning

Your code works wonderfully !!!!. BUT, I now have a little problem with the configuration of the date, as I want, give the option select by year or by month or by day, so I like to "split" the date, since currently only and full board is listed in the format yearmonthday is not optimal if a user wants to select the entire month. You understand me?

I have tried to format the variable "Transaction_date" with the following code:

Year (Date#(Num(Transaction_date),'YYYYMMDD'))&Num(Month(Date#(Num(Transaction_date),'YYYYMMDD')),00) AS YearMonth,

Year(Date#(Num(Transaction_date),'YYYYMMDD')) as Year,

Month(Date#(Num(Transaction_date),'YYYYMMDD')) as Month,

Week(Date#(Num(Transaction_date),'YYYYMMDD')) as Week,

Day(Date#(Num(Transaction_date),'YYYYMMDD')) as Day,

WeekDay(Date#(Num(Transaction_date),'YYYYMMDD')) as WeekDay

But not responding.

Thank you very much for all the help you gave me these days!

A hug and a very good start this week for you.

Karen