topic Dynamic current month year in QlikView

Dynamic current month year

davyqliks — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Dynamic current month year

kumarkp412 — Thu, 04 Aug 2016 12:24:17 GMT

Hi David,

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

Thanks

Kumar

Re: Dynamic current month year

Anil_Babu_Samineni — Thu, 04 Aug 2016 12:30:05 GMT

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

Re: Dynamic current month year

sunny_talwar — Thu, 04 Aug 2016 12:47:00 GMT

How about this:

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

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

Re: Dynamic current month year

jpenuliar — Thu, 04 Aug 2016 12:48:00 GMT

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

Re: Dynamic current month year

davyqliks — Thu, 04 Aug 2016 15:40:54 GMT

Thank you for the reply Kumar,

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

Thank you

Re: Dynamic current month year

davyqliks — Thu, 04 Aug 2016 15:45:55 GMT

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

Re: Dynamic current month year

davyqliks — Thu, 04 Aug 2016 15:52:55 GMT

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

Re: Dynamic current month year

sunny_talwar — Thu, 04 Aug 2016 16:25:03 GMT

How about this (changes in red):

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

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

Re: Dynamic current month year

davyqliks — Fri, 05 Aug 2016 11:40:23 GMT

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