topic Re: sum to latest month in QlikView

sum to latest month

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

I have a field named month which give me data in a dd/mm/yyyy format. I then have a target column I want to just sum to latest month. i.e. if it is November there will be data for the full year but I want it to just show up to November.

One other thing is that I don't want this figure to change no matter what selection is made by the user from the various Listboxes I have.

Thanks

Re: sum to latest month

shraddha_g — Wed, 23 Nov 2016 11:52:45 GMT

If you Don't have YearStart Field then

sum({1<CalendarDate = {">=$(=Date(YearStart(Max(CalendarDate))))<=$(=Date(Max(CalendarDate)))"}>}Target)

else

sum({1<CalendarDate = {">=$(=Date(Max(YearStart)))<=$(=Date(Max(CalendarDate)))"}>}Target)

Re: sum to latest month

Anil_Babu_Samineni — Wed, 23 Nov 2016 12:34:06 GMT

Why can't simple set operator

And you should have month field from script

Month(Datefield) as Month

And then try to use this from your expression.

Sum({<Month = {'$(=Max(Month))'}>}Sales)

Re: sum to latest month

alwayslearning — Wed, 23 Nov 2016 12:38:42 GMT

Hi,

Both the above expressions return me the total year value and not up to the latest month.

I don't have a year start value. I do have a year field but it is not a date format and just a string yyyy

Re: sum to latest month

alwayslearning — Wed, 23 Nov 2016 12:41:39 GMT

Hi Anil,

Thanks for the help

This only returns me the latest month. I want a sum of all month's up to the latest.

Re: sum to latest month

Anil_Babu_Samineni — Wed, 23 Nov 2016 13:44:35 GMT

Then use something like this

Sum({<Month = {>=Month(YearStart(Today())) <= Max(Month)}>}Sales)

Re: sum to latest month

MK9885 — Wed, 23 Nov 2016 14:29:34 GMT

Use the below in Master Calendar script

If( Date > monthstart(addmonths(today(),-10)) and Date <= today(),1)

as [Rolling 11],

And in your expression use

=Sum({<[Rolling 11]={1}, your dimension/measure>}Sales)

Rolling 11 will only give you data only until Nov-2016.

Re: sum to latest month

Anonymous — Thu, 24 Nov 2016 04:52:32 GMT

Hi Kwok,

First convert that date into month like :- Date(DateField,'YYYY-MM')

now raise a flag like if(interval(date(today(),'YYYY-MM'),Date(DateField,'YYYY-MM'))=0,'CurrnetMonth','PreviousMonth') As MonthStatus

Now u can use this flag in your expression by giving filter MonthStatus='CurrentMonth' .

Thanks,

Re: sum to latest month

shraddha_g — Thu, 24 Nov 2016 04:57:37 GMT

Expression will give you values from year start to till date

If max date in your calendar data is 21st November then that expression will give you data from 1st jan till 21st November.

When you say upto November what does it actually mean?

Also check what is max date in your calendar?

Re: sum to latest month

shraddha_g — Thu, 24 Nov 2016 04:59:17 GMT

If your calendar has future dates,then it will consider those dates also while calculating max date.

Re: sum to latest month

alwayslearning — Fri, 25 Nov 2016 12:07:41 GMT

Hi Shraddha,

Well all the dates will be 1st of the months. As my data actually came from a string value of two separate columns of month and year, which I converted into a dd-mm-yyyy value.

As it is target value, there is data and dates going to end of the year. i.e. there is data in Dec but I would want the expression to sum to just the latest month.

Thanks

Re: sum to latest month

alwayslearning — Mon, 28 Nov 2016 12:36:13 GMT

Hi Arvind,

I don't have a master calendar in my script for this dashboard.

Can I place this anywhere else in my script? Or Can you show me a quick way to insert it as I am still fairly new to Qlik so I'm not to sure.

Thanks

Re: sum to latest month

alwayslearning — Mon, 28 Nov 2016 12:54:55 GMT

Hi Pooja,

Could you put this into context and a qlikview file for me?

As I'm unsure how to raise a flag, or use a created field in the same load area in my script. As I'm still fairly new to Qlik

Thanks