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: 
KumaRan
Contributor III
Contributor III

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
sunny_talwar

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;

 

View solution in original post

15 Replies
shiveshsingh
Master
Master

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

sunny_talwar

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Hi

Thanks for reply,

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

shiveshsingh
Master
Master

Can you share sample data?

KumaRan
Contributor III
Contributor III
Author

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

Capture.PNG

sunny_talwar

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
Contributor III
Contributor III
Author

 
sunny_talwar

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