Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Krish
Contributor III
Contributor III

Accumulation in Bar chart - null value display

I am creating a stacked bar chart in QlikSense and using full Accumulation based on dimension. The X axis has Month as dimension. There are few values which are null/zero in a particular month. I am trying to show the same number as previous month for that value even if it is zero in current month.

Say for example, value for X is 100 in Jan, 0 in feb and 2 more added in Mar (102 is the total accumulated value in March), I want to show the data as 100 in Jan & Feb and 102 in March. How can this be achieved?

Labels (2)
1 Solution

Accepted Solutions
vikasmahajan

Hi,

Did you implement Master Calendar ?

MasterCalendar:
LOAD
    OrderDate AS OrderDate,
    WEEK(OrderDate) AS Week,
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    DAY(OrderDate) AS Day,
    WEEKDAY (OrderDate) AS Weekday,
    'Q' & CEIL(MONTH(OrderDate) / 3) AS Quarter,
    DATE(MONTHSTART(OrderDate), 'MMM-YYYY') AS MonthYear,
    WEEK(OrderDate) & '-' & YEAR(OrderDate) AS WeekYear ;

 

Load
  Date(MinOrderDate+IterNo()-1) as OrderDate
While MinOrderDate+IterNo()-1  <= MaxOrderDate;
  
LOAD
    Date(MIN(FieldValue('OrderDate',recno()))) AS MinOrderDate,
    Date(MAX(FieldValue('OrderDate',recno()))) AS MaxOrderDate
autogenerate FieldValueCount('OrderDate');

 

 

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.

View solution in original post

5 Replies
vikasmahajan

Hi,

Did you implement Master Calendar ?

MasterCalendar:
LOAD
    OrderDate AS OrderDate,
    WEEK(OrderDate) AS Week,
    YEAR(OrderDate) AS Year,
    MONTH(OrderDate) AS Month,
    DAY(OrderDate) AS Day,
    WEEKDAY (OrderDate) AS Weekday,
    'Q' & CEIL(MONTH(OrderDate) / 3) AS Quarter,
    DATE(MONTHSTART(OrderDate), 'MMM-YYYY') AS MonthYear,
    WEEK(OrderDate) & '-' & YEAR(OrderDate) AS WeekYear ;

 

Load
  Date(MinOrderDate+IterNo()-1) as OrderDate
While MinOrderDate+IterNo()-1  <= MaxOrderDate;
  
LOAD
    Date(MIN(FieldValue('OrderDate',recno()))) AS MinOrderDate,
    Date(MAX(FieldValue('OrderDate',recno()))) AS MaxOrderDate
autogenerate FieldValueCount('OrderDate');

 

 

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Krish
Contributor III
Contributor III
Author

Hi Vikas,

 Thank you for your response. One quick question, will the master calendar resolve work in Quarter as well? If the data is 100 in Q1 and 0 in Q2, but I want that to display as 100 in Q1 and Q2.

vikasmahajan

yes , Master calendar purpose is to show 0 zero values for those dates where we don't have sales entry.

Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
Krish
Contributor III
Contributor III
Author

But I cannot use this date field in Master calendar as there is another field used from the source. The same is working in QlikView when Full Accumulation checkbox is selected at measure level, but its not the case in Qliksense.

Krish
Contributor III
Contributor III
Author

I did try using Master calendar for that date field and tested, but the result is still the same. I do not see the previous month's values for the dimension when current month is 0