Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

KumaRan
New Contributor II

Cumulative with two dimension

HI  all,

I am trying to create a cumulative bar chart for two dimension. Here i have taken brand and day as dimension.Sales as measure

say for example brand A &Brand B for 10 days.

Brand A doesn't have transaction on Day 5. actually 

Day      sales   Cumulative                                                                       Day      sales   Cumulative                                                                             1             1             1                                                                                          1             1             1

2              2           3                                                                                          2              2           3

3              3           6                                                                                         3              3           6 

4             4           10                          Required O/P   --->                        4             4           10

6             6           16                                                                                        5           0              10

7             7           23                                                                                        6             6           16   

8            8             31                                                                                        7             7           23

 

sample data and application attached here.

Thanks in anticipation

1 Solution

Accepted Solutions

Re: Cumulative with two dimension

All you might need to do is this (can be very expensive) 

TempTable:
LOAD DISTINCT Brand
Resident Table;

Join (TempTable)
LOAD DISTINCT Day
Resident Table;

Once you create a new table where you create a Cartesian join between Brand and Day, you can then concatenate it to your main table

Concatenate (Table)
LOAD Brand,
 Day
Resident TempTable;

 

15 Replies
shiveshsingh
Honored Contributor

Re: Cumulative with two dimension

T:LOAD * INLINE [
Day,sales,Cumulative
1,1,1
2,2,3
3,3,5
4,4,8
6,6,3
7,7,0
8,8,2];

Concatenate(T)
load * inline
[Day,sales,Cumulative
5,5,5] ;

Re: Cumulative with two dimension

You are looking to do this in the script or directly in the front end? Also, I don't see any sample application attached?
KumaRan
New Contributor II

Re: Cumulative with two dimension

Both are fine.

Actually my requirement is, 

I have to current MTD sales in bar chart as Cumulative.

image i have attached is for monthly cumulative.same way i want it for daily cumulative for Current month.

I achieved cumulative.but the issue is if particular brand is not having transaction on that day, that brand itself is not appearing on that day.if we get zero value we can achieve this?

Capture.PNG

KumaRan
New Contributor II

Re: Cumulative with two dimension

Hi

Thanks for reply,

The solution which you suggested will work for static data.But my scenario is dynamic.

shiveshsingh
Honored Contributor

Re: Cumulative with two dimension

Can you share sample data?

KumaRan
New Contributor II

Re: Cumulative with two dimension

i am trying to attach excel and qvw sample. but its not getting attached. But it is showing the following error

Capture.PNG

Re: Cumulative with two dimension

Are you trying to attach a qvw or qvf file? There is a known issue uploading a qvf file. The work around is to zip the file and then upload it.
KumaRan
New Contributor II

Re: Cumulative with two dimension

 

Re: Cumulative with two dimension

This is a case of missing data... unfortunately, unless you have a row with the combination of Brand and Day available in the dashboard, this probably isn't going to work....

But having said that, this may be a simple version of a complex expression which might be removing some stuff because of set analysis in it, when infact the combination of Brand and Day does exists. If that is the case, you can try this

Aggr(RangeSum(Above(Sum(Sales) + Sum({1} 0), 0, RowNo())), Brand, (Day, (Numeric)))

If this doesn't work, then I am afraid you will have to make sure that each and every combination of Brand and Day exists in the application

Community Browser