topic Re: Help needed in building a formula for MTD with today() function in QlikView

Help needed in building a formula for MTD with today() function

fashid — Thu, 23 Apr 2015 08:56:44 GMT

Hi,

I am creating MTD YTD values using the below expression

=Count({<CalendarDate={">=$(=(MonthStart(today()))<=$(=(today())))"}>}DISTINCT ENQUIRY_ID)

but i am getting zero result.

I need to use today function only as i have inconsistent dates in the application.

Could anybody help me out in this

Regards,

Nadeem

Re: Help needed in building a formula for MTD with today() function

PrashantSangle — Thu, 23 Apr 2015 09:11:02 GMT

Hi,

Your expression is ok,

Just check date format.

Regards

Re: Help needed in building a formula for MTD with today() function

Anonymous — Thu, 23 Apr 2015 09:22:01 GMT

hi yes max dreamer is right,

you have written correct statement

just check CalendarDate format

also try using

ount({<num(CalendarDate)={">=$(=(num(MonthStart(today())))<=$(=(num(today()))))"}>}DISTINCT ENQUIRY_ID)

lets see if it works.

Thanks

BKC

Re: Help needed in building a formula for MTD with today() function

PrashantSangle — Thu, 23 Apr 2015 09:34:11 GMT

Hi,

I think In set Analysis you can not convert the comparing field

Just check num(CalendarDate) Will not work in set analysis.

Regards

Re: Help needed in building a formula for MTD with today() function

Thu, 23 Apr 2015 09:37:42 GMT

=(count( {$<CalendarDatenum={">=$(=(MTDStartDate))<=$(=(MTDEndDate))"}>} distinct ENQUIRY_ID))

MTDStartDate==num(MonthStart(today()))

MTDEndDate==num(today())

Re: Help needed in building a formula for MTD with today() function

Anonymous — Thu, 23 Apr 2015 09:45:13 GMT

yes you are right my bad ..

Thank you Max

Re: Help needed in building a formula for MTD with today() function

ankitaag — Thu, 23 Apr 2015 10:22:43 GMT

Hey,

Try

=Count({<CalendarDate={">=$(=vMonthStart)<=$(=vMonthEnd)"}>}DISTINCT ENQUIRY_ID)

create 2 variables

vMonthStart ==Date(MonthStart(today()),'DD-MM-YYYY')

vMonthEnd= =Date(today(),'DD-MM-YYYY')

Use the same dateformat as of your CalendarDate

Thanks and Regards,

Ankita

Re: Help needed in building a formula for MTD with today() function

fashid — Thu, 23 Apr 2015 11:22:59 GMT

Wow thanks guys for showing different methods .

Now i do not understand which answer do i mark as correct

Re: Help needed in building a formula for MTD with today() function

ankitaag — Thu, 23 Apr 2015 11:32:11 GMT

You need to mark 1 answer as correct so as to close this discussion.

Re: Help needed in building a formula for MTD with today() function

satishkurra — Thu, 23 Apr 2015 11:39:23 GMT

See if this can help you..

Load the below:

LOAD * Inline [

Date,Product,Sales

01/01/2014,Apple,100

02/01/2014,Samsung,200

03/01/2014,Nokia,900

04/01/2014,Apple,100

05/01/2014,Samsung,200

06/01/2014,Nokia,900

01/01/2015,Samsung,300

02/01/2015,Apple,400

03/01/2015,Nokia,300

03/05/2015,Apple,400

07/01/2015,Samsung,300

08/01/2015,Apple,400

09/01/2015,Nokia,300

10/05/2015,Apple,400 ];

Create straight tables like below

1. No dimension

2. Expressions as below

YTD and YTD -1

YTD: Sum({$<Date={">=$(=Num(YearStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

YTD-1: SUm({$<Date={">=$(=Num(YearStart(Max(Date),-1)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

QTD and QTD -1

QTD: Sum({$<Date={">=$(=Num(QuarterStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

QTD-1 : SUm({$<Date={">=$(=Num(QuarterStart(Max(Date),-4)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

WTD and WTD -1

WTD: Sum({$<Date={">=$(=Num(WeekStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

WTD -1: SUm({$<Date={">=$(=Num(WeekStart(Max(Date),-52)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

MTD and MTD -1

MTD: Sum({$<Date={">=$(=Num(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

MTD -1: SUm({$<Date={">=$(=Num(MonthStart(Max(Date),-12)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

Thanks

Satish

Re: Help needed in building a formula for MTD with today() function

fashid — Thu, 23 Apr 2015 11:41:28 GMT

Hi Max,

Thanks for replying

i had to use

=Count({< DateNum={">=$(=Num(MonthStart(today())))<=$(=today()))"}>}DISTINCT ENQUIRY_ID).

I did not understand why the field with the dates did not work and why i had to deliberately convert it to num.

Regards,

Nadeem

Re: Help needed in building a formula for MTD with today() function

fashid — Thu, 23 Apr 2015 11:53:22 GMT

Hi Ankita,

I am marking your answer as correct for the clear explanation that you gave

"Use the same dateformat as of your CalendarDate"

but thanks everybody for their contribution

Regards,

Nadem