Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Date Variable

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.

Labels (2)
8 Replies
Highlighted
Creator
Creator

 

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

 

2.JPGdate ex.JPG

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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?

Highlighted
Partner
Partner

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

Highlighted
Creator
Creator

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 -.

results.JPGvCurrMonth.JPG

 

Highlighted
MVP
MVP

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)));  

Highlighted
Creator
Creator

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?

 

Highlighted
MVP
MVP

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])