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: 
300sss300
Creator
Creator

How to add Quarter as Dimensions & Bifurcation of cumulative balance

HI

I have data similar as below

Month     Amount

Jan          10

Feb          25

Mar          15

Apr          45

May         60

Jun          35

Jul            45

Aug          80

Sep          100

Oct          105

Nov          90

Dec          80

Q1: How I can add Quarter 1 (Jan -Mar) Q2 (Apr-Jun) Q3 and Q4 as Dimensions or in Coding as Fields 

Q2: Above balances are cumulative balances, How I can bifurcate these balances as period balances for each month in Coding or in expression as formula to show in chart or table e.g For Feb Balance should be 25-10 = 15 vice versa  

Regards

2 Solutions

Accepted Solutions
brunobertels
Master
Master

Hi

Try this in your Script :

Q1: How I can add Quarter 1 (Jan -Mar) Q2 (Apr-Jun) Q3 and Q4 as Dimensions or in Coding as Fields :

'Quart ' & Num(Ceil(month(date#("Month",'MMM'))/3),'(ROM)0') as Quarter,

Q2: Above balances are cumulative balances, How I can bifurcate these balances as period balances for each month in Coding or in expression as formula to show in chart or table e.g For Feb Balance should be 25-10 = 15 vice versa  :

add this in your graph as mesure :

Sum(Amount)-above(sum( Amount))

this will give you :

hope it helps

Bruno

View solution in original post

300sss300
Creator
Creator
Author

Date Amount
1-Jan-15 10
1-Feb-15 15
1-Mar-15 20
1-Apr-15 33
1-May-15 45
1-Jun-15 15
1-Jul-15 35
1-Aug-15 83
1-Sep-15 10
1-Oct-15 24
1-Nov-15 55
1-Dec-15

66

I am using this in Script

LOAD Date,

  Year (Date) as Year,

     Month (Date) as Month,

     Day (Date) as Day,

     MonthName (Date) as YearMonth, 

     Amount

From...................

View solution in original post

8 Replies
Not applicable

Using match eg

pick(match(Month,'Jan','Feb','Mar','Apr')

,'Q1'

,'Q1'

     ,'Q1'

     ,'Q2'

)

brunobertels
Master
Master

Hi

Try this in your Script :

Q1: How I can add Quarter 1 (Jan -Mar) Q2 (Apr-Jun) Q3 and Q4 as Dimensions or in Coding as Fields :

'Quart ' & Num(Ceil(month(date#("Month",'MMM'))/3),'(ROM)0') as Quarter,

Q2: Above balances are cumulative balances, How I can bifurcate these balances as period balances for each month in Coding or in expression as formula to show in chart or table e.g For Feb Balance should be 25-10 = 15 vice versa  :

add this in your graph as mesure :

Sum(Amount)-above(sum( Amount))

this will give you :

hope it helps

Bruno

300sss300
Creator
Creator
Author

Thanks for the reply.

this is to be used in "Calculated Dimension"

How can i Add Q1, Q2, Q3, Q4 in "Edit Script" so that it should appear as separate fields.

300sss300
Creator
Creator
Author

Thanks Bruno !

2nd one is working but this is not working

'Quart ' & Num(Ceil(month(date#("Month",'MMM'))/3),'(ROM)0') as Quarter,


showing an error


300sss300
Creator
Creator
Author

Date Amount
1-Jan-15 10
1-Feb-15 15
1-Mar-15 20
1-Apr-15 33
1-May-15 45
1-Jun-15 15
1-Jul-15 35
1-Aug-15 83
1-Sep-15 10
1-Oct-15 24
1-Nov-15 55
1-Dec-15

66

I am using this in Script

LOAD Date,

  Year (Date) as Year,

     Month (Date) as Month,

     Day (Date) as Day,

     MonthName (Date) as YearMonth, 

     Amount

From...................

brunobertels
Master
Master

Hi

First it seems that your date field has not the same format : see below when adding in Qlik Sense :

some date are 01/01/2015 some other are 1-feb-15

So ones are string other are integer

could you chek your data and the format of your date field.

Second : to add a calculated dimension of quarter just add this in your script like this :

LOAD Date,

  Year (Date) as Year,

'Quart ' & Num(Ceil(month("Date",'MMM'))/3),'(ROM)0') as Quarter,


     Month (Date) as Month,

     Day (Date) as Day,

     MonthName (Date) as YearMonth, 

     Amount

From...................

Bruno

300sss300
Creator
Creator
Author

Thanks!!!

Dates are in the same format.

Still not working

brunobertels
Master
Master

oups,

try this

  'Quart ' & Num(Ceil(month("date")/3),'(ROM)0') as Quarter,