Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What do you mean by day wise qty?
You mean day 1 , 2 , 3 up to 30 or 31 depending upon Month
or
1 ,2 , 3 ...7 (weekly)
Also, provide sample data, if possible.. Need to work out in the script...
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
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
need sample data please.
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
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.
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
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.
=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