Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator III

Current month data

Trying to figure out how to calculate current month processes.

expression:

=Sum({$<Status_Dash={'Completed'}, Month={"$(Month(addmonths(today(),-0)))"}>} Processed_Dash)

the return value should be 4342, but it is returning all data from "completed" in the database, including previous months.

current month.PNG

20 Replies
joshrussin
Creator III
Creator III
Author

This returned a 0

sunny_talwar

Can you share the exact script you have used to create your Month field?

jcampbell474
Creator III
Creator III

Use this one.  I had an incorrect field in the first one:

sum({<Status_Dash={'Completed'},Date_Dash={">=Date(MonthStart(Today(1))) <=Date(MonthEnd(Today(1))"}>}Processed_Dash)

joshrussin
Creator III
Creator III
Author

Still a 0

joshrussin
Creator III
Creator III
Author

So instead of redefining Month, I created a variable called 'vLM' and its expression is:

Month(Date_Dash)

From there I am using this expression to get my answer:

=Sum({$<Status_Dash={'Completed'}, vLM = {"$(=Month(Today()))"}>} Processed_Dash)

sunny_talwar

You cannot use functions on the right hand side of the set modifier.... you will need to create Month field in the script

Month(Date_Dash) as Month

and then use this field in your set analysis

=Sum({$<Status_Dash={'Completed'}, Month= {"$(=Month(Today()))"}>} Processed_Dash)

jcampbell474
Creator III
Creator III

Sunny will not steer you wrong.  I'd go with whatever he advises.

But if you want to pursue this solution for whatever reason, I would need to see the script used to create Date_Dash.

joshrussin
Creator III
Creator III
Author

Still new to using the scripting.

But this is what I have.

LOAD
   Date_Dash,
   Program_Dash,
   UOM_Dash,
   Status_Dash,
   Ave_Cost_Dash,
   Processed_Dash
FROM [lib://AttachedFiles/OB_Summary_Data.xlsx]
(ooxml, embedded labels, table is OB_Dash_Data);

Load
Month(Date_Dash) as Month;

This shows an error stating "Date_Dash" not found

sunny_talwar

Like this

LOAD

  Date_Dash,

  Month(Date_Dash) as Month,

  Program_Dash,

  UOM_Dash,

  Status_Dash,

  Ave_Cost_Dash,

  Processed_Dash

FROM [lib://AttachedFiles/OB_Summary_Data.xlsx]

(ooxml, embedded labels, table is OB_Dash_Data);

joshrussin
Creator III
Creator III
Author

You most certainly a huge help! Thanks for your patience and knowledge!