Dynamic current month year

[davyqliks]

HI,

I have my reporting sorted but have to change the month year every month. I was looking to make this dynamic. my data is loaded like so:

LOAD [Inv NO.] as [INV NO],

     [SO NO.] as [INV SO NO] ,

     [PO NO.] as [Inv PO NO],

     [Customer NO.]  as [Customr Name],

     [Item Name],

     Status as [Inv Status],

     CS as [ERP USER],

     [Outstanding Amount] as [Invoive Total],

     DATE(DATE([Issue Date],'DD/MM/YYYY hh:mm:ss[.fff]'),'DD-MM-YY' )as Date,

Year([Issue Date]) as Year,

          Month([Issue Date]) as Month,

          Day([Issue Date]) as Day,

           Date(monthstart([Issue Date]), 'MMM-YYYY') as MonthYear

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

and here is the expression hoping to give current month sales(for a starting point).

=Sum({1<Year={$(=Max(Year))},Month={$(=Max(Month))}>}[Invoice Total])

In the load i also have the following:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

load dual(String,Num) as Month inline

[String,Num

Jan,1

Feb,2

Mar,3

Apr,4

May,5

June,6

July,7

Aug,8

Sept,9

Oct,10

Nov,11

Dec,12

];

This is returning blanks.

Can any body give ma a suggestion on where i am going wrong. The syntax on the page all looks correct.

Thank you

[kumarkp412]

Hi David,

Can you elaborate you question please.Your not getting any data or it was not mapping?

Thanks

Kumar

[Anil_Babu_Samineni]

Can you post application, Try to share expected O/P also, I am not fully understand your issue

[sunny_talwar]

How about this:

=Sum({1<MonthYear = {$(=Date(Max(MonthYear), 'MMM-YYYY'))>} [Invoice Total])

or

=Sum({1<MonthYear = {$(=Date(MonthStart(Today()), 'MMM-YYYY'))>} [Invoice Total])

[jpenuliar]

Hi David,

Have a look at Lee Mathews example on working with Dates:

QlikView How To (or Tips & Tricks) Application | Qlik Community

hint: you can make use of  Flags to mark the dates which are within the the current Month.

In Lee's example he flag the Calendar field, see below:

if(monthstart(TempDate)=monthstart(AddMonths($(vToday), -1)), 1, 0) AS CurMTDFlag,

  if(monthstart(TempDate)=monthstart(AddMonths($(vToday), -2)), 1, 0) AS PriorMTDFlag,

The flags are used in SET Analysis afterwards.

Regards,

Jonathan Penuliar

[davyqliks]

Thank you for the reply Kumar,

I mean the expression syntax looks fine but the result for this expression is all zeros.

Thank you

[davyqliks]

Thank you Sunny,

The 2 suggestions given return

Error in Set modifier as hoc element list: ',' or ')' expexted

yet the syntax looks fine on the screen. Thankyou

[davyqliks]

Thank you for the link, i will take a look and reply if i find the solution.

[sunny_talwar]

How about this (changes in red):

=Sum({1<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>} [Invoice Total])

or

=Sum({1<MonthYear = {"$(=Date(MonthStart(Today()), 'MMM-YYYY'))"}>} [Invoice Total])

[davyqliks]

Thank you again Sunny,

Both returns zero for all lines. i am sure your suggestion is good. the issue must be with my date setup. Do you know of any good date guides for dynamic reporting

this month, next month ytd previous yrs etc.

I have searched and can find nothing with a guide to setting up the date so that the expressions return the corrcet results.

I believe i have defined DATE and date breakdown using this.

DATE(DATE([Issue Date],'DD/MM/YYYY hh:mm:ss[.fff]'),'DD-MM-YY' )as Date,

Year([Issue Date]) as Year,

          Month([Issue Date]) as Month,

          Day([Issue Date]) as Day,

           Date(monthstart([Issue Date]), 'MMM-YYYY') as MonthYear

and that my date settings are correct.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

My original code returns the correct figures (which you helped me achieve) Thank you

sum({1<[MonthYear]={'Jul-2016'}>}[Invoive Total])

Thank you