topic Re: YTD MTD in QlikView

YTD MTD

Mon, 16 Feb 2015 05:48:16 GMT

Hi to all,

(sum({<BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)
-
(sum({<BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)
-
(sum({<BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

I taken label name as " Gross Weight" for above expression...please help me

I want find out YTD,MTD& WTD for above Expression.

How can I do???

Please help me out this issue.

Thanks in Advance.

regards

Chandra.

Re: YTD MTD

jagan — Mon, 16 Feb 2015 06:04:42 GMT

Hi,

If you have a date field then use below expression

YTD:

(sum({<Date={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

MTD:

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

WTD:

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

Note: Replace Date with your actual field name.

Regards,

Jagan.

Re: YTD MTD

Mon, 16 Feb 2015 06:30:24 GMT

Hi Jagan,

Thanks so much your reply.

it is great helpful for me.

if you dont mine give me like below...

I have Date field is "BillDate"

i have data from "01/04/2014" to 01/03/2015 (DD/MM/YYYY)

i have regions like AP,KARNATAKA

i have 7 regions

please give me complete expressions now

Thanks in advance.

Regards

chandra.

Re: YTD MTD

Mon, 16 Feb 2015 06:43:20 GMT

hi,

What is the Today() here

please explain me

Thanks in Advance

Re: YTD MTD

jagan — Mon, 16 Feb 2015 07:16:03 GMT

Hi,

check this

YTD:

(sum({<BillDate={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=YearStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

MTD:

(sum({<BillDate={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

WTD:

(sum({<BillDate={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<BillDate={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

Today() gives the current date i,e, today's date.

YTD means - Current year uptodate i,e, 1-Jan-2015 to till date (16-Feb-2015)

Regards,

Jagan.

Re: YTD MTD

Mon, 16 Feb 2015 07:45:44 GMT

Hi Jagan,

I am trying what you given expressions but i am getting same values for YTD,MTD AND WTD,

So where can i done mistake???

Re: YTD MTD

jagan — Mon, 16 Feb 2015 08:28:01 GMT

Hi,

I think your data is same for MTD, WTD and MTD. YTD expression works for the current year.

Regards,

Jagan.

Re: YTD MTD

Mon, 16 Feb 2015 08:55:32 GMT

hi Jagan,

wtd and mtd same in the sense i cant get that word??

Please explain me

thanks

Re: YTD MTD

jagan — Mon, 16 Feb 2015 09:09:18 GMT

Hi,

What value you are getting for YTD, MTD and WTD?

Can you attach some sample data and your expected ouput, so that it would be easier to give solution.

Regards,

Jagan.

Re: YTD MTD

Mon, 16 Feb 2015 09:46:15 GMT

Hi jagan,

YTD is getting currect values but i m not getting currect values for MTD & WTD,

I used what you given those expressions only i used

thanks for consideration of my requests

Re: YTD MTD

jagan — Mon, 16 Feb 2015 11:05:20 GMT

Hi,

Did you changed MonthStart and WeekStart() in your expressions

MTD:

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=MonthStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

WTD:

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

If not working attach some sample file.

Regards,

Jagan.

Re: YTD MTD

Mon, 16 Feb 2015 13:42:10 GMT

Hi Jagan,

I am getting Values for Ytd and Mtd also...

I will try for that anyway i am very thankful to you...

thanks.

Regards

Chandra.

Re: YTD MTD

jagan — Tue, 17 Feb 2015 02:09:22 GMT

Hi,

Please close this thread by giving Correct and Helpful answers to the useful posts, so that it would be helpful for others who are looking for similar solutions.

Go through the Set Analysis topic in Qlikview Help file (F1), so that you can understand this type of expressions easily.

Regards,

Jagan.

Re: YTD MTD

Tue, 17 Feb 2015 11:36:09 GMT

Hi Jagan,

I am very thankful to your replies.

Sorry for the delay.

As you said i closed this

Many thanks to you

Regards

Chandra.

Re: YTD MTD

jagan — Tue, 17 Feb 2015 14:47:52 GMT

Close this thread by clicking Correct Answer and Helpful Answers buttons to the useful posts.

Regards,

jagan.

Re: YTD MTD

Tue, 17 Feb 2015 15:02:23 GMT

Hi Jagan,

Can you please give me that WTD expression

Please help me

Re: YTD MTD

jagan — Tue, 17 Feb 2015 15:11:02 GMT

WTD:

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Date={'>=$(=WeekStart(Today()))<=$(=Today())'}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

Regards,

Jagan

Re: YTD MTD

Tue, 17 Feb 2015 15:13:58 GMT

Hi Jagan,

For following expression i want get the WTD value in Text Object.

I am getting the values if i select.

But without Selection i want get the Present Month Week to date value in the sense feb month

i have the date field from Apr/01/2014 to Mar/01/2015.

But i have the data from Apr/01/2015 to Feb/05/2015.

How Can i get this please help me out this issue.

Following is my expression.

=Num((sum({<Year=, Quarter=, Month=, Week=, [BILL DATE]=,BILLDATE={">=$(=Date(WeekStart(Max(BILLDATE)-1)))<=$(=Date(Max(BILLDATE)))"}, BILLDETAILENTRYTYPE = {'B'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Year=, Quarter=, Month=, Week=, [BILL DATE]=,BILLDATE={">=$(=Date(WeekStart(Max(BILLDATE)-1)))<=$(=Date(Max(BILLDATE)))"}, BILLDETAILENTRYTYPE = {'R'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}>}BILLDETAILGROSSWEIGHT)/WeightFlag)

(sum({<Year =, Quarter=, Month=, Week=, [BILL DATE]=,BILLDATE={">=$(=Date(WeekStart(Max(BILLDATE)-1)))<=$(=Date(Max(BILLDATE)))"}, BILLDETAILENTRYTYPE = {'O'}, BILLCANCELLED = {'N'}, BILLESTIMATE = {'N'}, URDPURCHASE = {'Y'}>}BILLDETAILGROSSWEIGHT)/WeightFlag),'##.00')

Thanks in Advance.

Regards

Chandra

in App Development • • Reply •

Mark unread Hide new activity

0 replies

Reply to original discussion