topic Re: Current month data in QlikView

Current month data

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

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.

Re: Current month data

Anil_Babu_Samineni — Wed, 05 Jul 2017 16:57:04 GMT

I am not sure why Month involved here, you could use this to achieve

Sum({<Status_Dash = {'Completed'}>} Processed_Dash)

Re: Current month data

Anil_Babu_Samineni — Wed, 05 Jul 2017 16:59:14 GMT

From your expression, Synthetically this?

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

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

Re: Current month data

joshrussin — Wed, 05 Jul 2017 16:59:46 GMT

Because this is just a snapshot of this months data. There is previous data in the database as well.

Re: Current month data

joshrussin — Wed, 05 Jul 2017 17:03:44 GMT

I've tried both of those and they both return everything 'completed' from the database.

Re: Current month data

sunny_talwar — Wed, 05 Jul 2017 17:08:31 GMT

How is your Month field created in the script?

Re: Current month data

Anil_Babu_Samineni — Wed, 05 Jul 2017 17:10:16 GMT

My first expression will return 4342, Only. Aren't you?

Re: Current month data

sunny_talwar — Wed, 05 Jul 2017 17:10:24 GMT

May be this

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

Where Month is created in the script like this

Month(Date_Dash) as Month

Re: Current month data

jcampbell474 — Wed, 05 Jul 2017 17:12:49 GMT

Can you try:

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

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)

Re: Current month data

joshrussin — Wed, 05 Jul 2017 17:17:21 GMT

Hmmm, I tried this and still returns all of the collected data. Not just this month.

Re: Current month data

joshrussin — Wed, 05 Jul 2017 17:18:36 GMT

This returned a 0

Re: Current month data

sunny_talwar — Wed, 05 Jul 2017 17:20:10 GMT

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

Re: Current month data

jcampbell474 — Wed, 05 Jul 2017 17:20:35 GMT

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)

Re: Current month data

joshrussin — Wed, 05 Jul 2017 17:23:16 GMT

Still a 0

Re: Current month data

joshrussin — Wed, 05 Jul 2017 17:25:44 GMT

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)

Re: Current month data

sunny_talwar — Wed, 05 Jul 2017 17:27:36 GMT

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)

Re: Current month data

jcampbell474 — Wed, 05 Jul 2017 17:48:05 GMT

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.

Re: Current month data

joshrussin — Wed, 05 Jul 2017 18:13:58 GMT

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

Re: Current month data

sunny_talwar — Wed, 05 Jul 2017 18:20:07 GMT

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

Re: Current month data

joshrussin — Wed, 05 Jul 2017 18:38:07 GMT

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