topic Re: cumulative total for the current month in QlikView

cumulative total for the current month

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

Hi all,

I need to show the current month current year cumulative total.

For example we are in month of may.

So total has to show all the sales till may.

the required output is this:

Month	CY Sales
May	6000

The data can be :

Month Sales Year

Jan 1000 2017

Feb 1000 2017

Mar 1000 2017

Apr 1000 2017

May 2000 2017

Apr 100 2016

May 200 2016

any help would be helpful

thanks

Anupama Jagan

Re: cumulative total for the current month

neelamsaroha157 — Tue, 09 May 2017 14:45:47 GMT

Check this out

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 18:21:24 GMT

Hi Neelam,

This is showing all the months right. I just need to populate only the current month

Month	CY Sales
May	6000

the current month should show the cumulative total of that year.

Thanks,

Anupama Mohanan

Re: cumulative total for the current month

neelamsaroha157 — Tue, 09 May 2017 18:26:27 GMT

Are you trying to have this in a Table or any KPI object??

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 18:36:51 GMT

In my dashboard we have few other fields like total claims, premium which I need to show the cumulative total for the current month. For example I have just taken sales for easy demo.

So the user wants to see only current month in a straight table but with the cumulative total.

Just like

Month Sales

May 6000

Kindly let me know if you need information regarding this,

Thanks,

Anupama Jagan

Re: cumulative total for the current month

neelamsaroha157 — Tue, 09 May 2017 18:50:06 GMT

Create one more field for Month Number or use Dual in Month field to get the numeric part of Month.

Then you can use

=Aggr(Max(MonthNum), Year) as Dimension

Sum(Sales) as Expression

Re: cumulative total for the current month

neelamsaroha157 — Tue, 09 May 2017 18:53:40 GMT

You can use the text objects well as an alternate.

Check out the attachment.

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 18:59:44 GMT

Hi Neelam,

Under the sales I got the correct value but the monthname should be shown . when we use this calculated dimension it shows the monthnum .

Thanks,

Anupama Jagan

Re: cumulative total for the current month

Digvijay_Singh — Tue, 09 May 2017 19:03:25 GMT

See if this sample can help. you may have field like monthno to be used to find max month.

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 19:19:02 GMT

Hi Digvijay,

When I tried I could got the result like this in my application

Month Sales

May 13700

- 13700

Instead of aggregating in the dimensions is it possible to do it via expression itself.?

Because the entire straight table is like this:

Month CYTD Sales PY Sales

May 6000 200

so what they want is only the CYTD should show the cumulative total of the current year. But the PY (previous year) should show the sales that has happened only in the month of may 2016.

Kindly let me know if you need more information.

Thanks,

anupama jagan

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 19:24:41 GMT

One way (of many) would be

Add a flag in your script for current year.

load *,

if(YourYearField=year(today()),1,0) as CurrYear

resident YourTable;

Then your expression would be

=sum(TOTAL Sale*CurrYear)

Then put a Dimension Limit of Largest 1. (Uncheck Show Others)

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 19:35:45 GMT

Hi Wallo,

When I tried that im getting the correct sales count but the month is shown as dec and not may

Thanks,

Anupama Jagan

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 19:52:16 GMT

Try this.

Added a CurrMonth flag in the script.

Add it as a hidden dimension in the chart. And put a dimension limit on that dimension only.

Re: cumulative total for the current month

Anonymous — Tue, 09 May 2017 20:04:43 GMT

For some reason im still not getting it.

I tried all the steps you have done in your application.

Im getting all the months and the same cumulative value populated in it.

Something like this

Month Sales

Jan 13700

Feb 13700

Mar 13700

Apr 13700

May 13700

Jun 13700

Jul 13700

Aug 13700

Sep 13700

Oct 13700

Nov 13700

Dec 13700

Re: cumulative total for the current month

Digvijay_Singh — Tue, 09 May 2017 20:26:19 GMT

You may do like this, since you want max month but don't want calculation in dimension, you can have expression for the max month, but then you need to hide your month dimension like this, you can hide dimension in the presentation tab -

Re: cumulative total for the current month

Digvijay_Singh — Tue, 09 May 2017 20:27:22 GMT

With changed labels -

Re: cumulative total for the current month

tyagishaila — Wed, 10 May 2017 10:33:42 GMT

Once try Rangesum() function for cumulative Sale.

Re: cumulative total for the current month

tyagishaila — Wed, 10 May 2017 10:37:39 GMT

Example-

TAB1:

LOAD * INLINE [

Month, Sale

1, 1000

2, 1000

3, 2000

4, 1000

5, 2000

];

TAB2:

NoConcatenate

Load *,RangeSum(Sale,Peek(SaleN)) As SaleN

Resident TAB1

Order by Month;

Drop Table TAB1;

exit Script;

Month	Sale	SaleN
1	1000	1000
2	1000	2000
3	2000	4000
4	1000	5000
5	2000	7000

Re: cumulative total for the current month

Anonymous — Wed, 10 May 2017 12:32:39 GMT

It worked ! Thanks a lot Digvijay

Re: cumulative total for the current month

shiveshsingh — Wed, 10 May 2017 14:28:27 GMT

please share the set analysis used in this problem