topic Re: Day wise Qty in QlikView

Day wise Qty

Fri, 25 Jul 2014 09:52:50 GMT

Hi All,

One of my requirement is to find out the Day wise Transaction qty so I have to display the qty on the selection on the day if there is no qty then i have to display 0 as qty . Can any one suggest me how I can Implement.

Regards

Deepak

Re: Day wise Qty

MK_QSL — Fri, 25 Jul 2014 09:59:24 GMT

What do you mean by day wise qty?

You mean day 1 , 2 , 3 up to 30 or 31 depending upon Month

1 ,2 , 3 ...7 (weekly)

Also, provide sample data, if possible.. Need to work out in the script...

Re: Day wise Qty

crusader_ — Fri, 25 Jul 2014 10:01:14 GMT

Hi Deepak,

Can you clarify the problem which you faced on?

You can't count transactions for the Day you have selected? or you can't get 0 as a result?

Also it would be great If you share your example.

Thanks.

Kind regards,

Andrei

Re: Day wise Qty

Fri, 25 Jul 2014 11:04:25 GMT

Hi Anderi,

My requirement is that when i select a particular day as a selection so if transaction is done on that i have to show the qty value otherwise i have to show zero and other requirement is that today is 24 th July so i have to show the MTD value and logic for the MTD is that last month Qty (Last month Qty /no of days of the last month)*no of days of current month suppose u select 20 july so u have to multiply with 20- i.e (100/30)*20 is the output.

If still u think u nt understand please let me know.

Regards

Deepak

Re: Day wise Qty

MK_QSL — Fri, 25 Jul 2014 11:08:27 GMT

need sample data please.

Re: Day wise Qty

Fri, 25 Jul 2014 11:10:36 GMT

Hi Manish,

Suppose u can refer as this as sample data.

Date	Qty
1/7/2014	200
4/7/2014	300
7/7/2014	700
8/7/2014	800
19/7/2014	600
25/7/2014	300

Regards

Deepak

Re: Day wise Qty

martynlloyd — Fri, 25 Jul 2014 11:19:45 GMT

First requirement seems very straightforward. In your script you will have

TransactionDate

Qty

To display 0 when no transaction exists, I would create a dummy value: if you already have a calendar generator simply add

... as TransactionDate,

0 as Qty

to the load.

If not look at "generating dates" as a topic

Regards.

Re: Day wise Qty

MK_QSL — Fri, 25 Jul 2014 11:31:03 GMT

Use below in your script…

==========================================

Temp:
Load
Date(Date#(Date,'D/M/YYYY')) as Date,
Qty
Inline
[
Date, Qty
1/6/2014, 220
14/6/2014, 330
17/6/2014, 100
24/6/2014, 400
1/7/2014, 200
4/7/2014, 300
7/7/2014, 700
8/7/2014, 800
19/7/2014, 600
24/7/2014, 300
];

MinMaxDate:
Load
Min(Date) as MinDate,
Max(Date) as MaxDate
Resident Temp;

Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));
Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));

DROP TABLE MinMaxDate;

Calendar:
Load
Date($(vMinDate) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Outer Join (Temp) Load * Resident Calendar;

Drop Table Calendar;

NoConcatenate
Sales:
Load Date, Month(Date) as Month, If(IsNull(Qty), 0, Qty) as Qty Resident Temp;

Drop Table Temp;

=====================================================

Create two text boxes with below exression

=IF(GetSelectedCount(Date),SUM(Qty))

=NUM(SUM({<Date = , Date = {">=$(=MonthStart(AddMonths(Max(Date),-1)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))"}>}Qty) * Day(MonthEnd(AddMonths(Max(Date),-1))) / Day(Max(Date)),'#,##0.00')

Regards,

Manish Kachhia

Re: Re: Day wise Qty

Fri, 25 Jul 2014 12:15:57 GMT

Dear Manish,

Thanks for your reply and appreciate your effort. But I am not getting the ouput . See in the attachment .

I select 24 the July so previous month Qty (June Qty ) is (1050/30)*24=840 is output if i select 20 the july (1050/30)*20 is the ouput.

Re: Day wise Qty

MK_QSL — Fri, 25 Jul 2014 12:58:36 GMT

=SUM({<Day = , Month = , Date = , Date = {">=$(=MonthStart(AddMonths(Max(Date),-1)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))"}>}Qty) *Day(Max(Date))/Day(MonthEnd(AddMonths(Max(Date),-1)))

and use Month as a dimension