Date Variable

[QlikBeginner1]

Hi,

I am currently developing a dashboard, and wanting to show two KPI's based on a date field which I am importing into Qlik via an Excel spreadsheet. One showing a count depending on this month and another showing last months. 

I have written the script and shown the value of the script in a new sheet, which gives the value i want as a Variable, and showing the Syntax is correct. However, when I try and declare this as a Variable in the data load editor, the variable is not being shown? An example is as below. 

Column I am trying to import:

Date/Time Open- Value : 30/05/2018 00:00:00

Let vStartLastMonthOpen = monthstart(addmonths([DateTimeOpened],-1));  (this is what I have added into the Qlik Data Load Editor)

IF('$(vDateBasis)'='dateOpen',
(Count({$<[Date/Time Opened]={">=$(=(vStartLastMonthOpen))<=$(=(vEndLastMonthOpen))"},
[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])),

(ignore the IF statement, this works, however the variables seem to not flow through?)

The column in the excel spreadsheet is a date/time format, not sure if this affects anything? 

I am new to Qlik Sense so sorry if this is a stupid question 🙂 thank you in advance.

[QlikBeginner1]

i have also attached some examples to show the values which are shown in the table using the expression, the same expressions which are used to populate the table are used to create the variables

[Kashyap_R]

Hi

Try this

Previous Month

Count({<[Date]={">=$(=MonthStart(Addmonths(max([Date]),-1)))<=$(=MonthEnd(Addmonths(Max([Date]),-1)))"},[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])

Current Month

Count({<[Date]={">=$(=MonthStart(Addmonths(max([Date])))<=$(=MonthEnd(Max([Date])))"},[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])

If you want it via variable

vCurrentMonth  : [Date]={">=$(=MonthStart(Addmonths(max([Date])))<=$(=MonthEnd(Max([Date])))"}

vPreviousMonth : [Date]={">=$(=MonthStart(Addmonths(max([Date]),-1)))                 <=$(=MonthEnd(Addmonths(Max([Date]),-1)))"}

Previous Month

Count({<$(vPreviousMonth),[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])

Current Month

Count({<$(vCurrentMonth),[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])

Hope this helps

Thanks

[QlikBeginner1]

Thank you so much for your time, i will give this a go now! What would you recommend? Using a variable for this or not?

[Kashyap_R]

Hi

If you want to use the current month and previous month condition with the same date in different KPI and charts

then go for variables else go with the set expression🙂

Hope this helps

Thanks

[QlikBeginner1]

Thank you for your help, however

It does not seem to work correctly, it seems to give a total count as the Previous Month and the Current Month as Null -.

[Kushal_Chawda]

In your script editor set your Timestamp format to [Date/Time Open] format

SET TimestampFormat='DD/MM/YYYY h:mm:ss';

Then you can change the variable like below then your expression should work because the variable value format should match with the [Date/Time Open] field format

Let vStartLastMonthOpen = timestamp(monthstart(addmonths([DateTimeOpened],-1)));  

[QlikBeginner1]

Hi Kush,

Thanks for your reply! I added the variable into the data load editor, and made sure that the TimeStamp Format is as you stated.

However, I believe I am having issues with how to syntax the expression (again im a newbie so apologies and thanks for all your patience). Could you assist with the syntax on how to build the expression?

[Kushal_Chawda]

Instead in script, try to create variables on front end. Make sure that the format of your actual date field and date coming from variable matches

// Last Month Variables
Let vStartLastMonthOpen = timestamp(monthstart(addmonths(max([DateTimeOpened]),-1)));  
Let vEndLastMonthOpen = timestamp(monthend(addmonths(max([DateTimeOpened]),-1)));  

// Current month variable
Let vStartMonthOpen = timestamp(monthstart(max([DateTimeOpened])));  
Let vEndMonthOpen = timestamp(max([DateTimeOpened])); 

CurrentMonth-
Count({$<[Date/Time Opened]={">=$(vStartMonthOpen)<=$(vEndLastMonthOpen)"},
[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])

LastMonth-
Count({$<[Date/Time Opened]={">=$(vStartLastMonthOpen)<=$(vEndLastMonthOpen)"},
[Status]={'Awaiting closed','Closed','Complaint Receipt','FOS: Awaiting FOS Decision','FOS: FOS referral received'}>}[Date/Time Opened])