Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am creating a dimension values bucket with overlapping values on chart and it not giving correct result.
Data is available in below form in QlikSense.
Month | Amount |
Jan | 10 |
Feb | 20 |
Mar | 30 |
Apr | 40 |
May | 50 |
Jun | 60 |
Month Bucket dimension has been created with below formula.
=if( Month='Jan', '1st Month',
if( match( Month, 'Jan', 'Feb', 'Mar'), 'Quarter',
if( match( Month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun'), 'Half Year',
) ) )
However, when I use it with Amount measure in Chart, it is only considering Jan month specific amount in '1st Month' bucket and not in next two buckets.
Basically, output is required in below form
Month Bucket | Amount |
1st Month | 10 |
Quarter | 60 |
Half Year | 210 |
It should calculate amount for each bucket considering earlier month(s) as
1st Month = 10
Quarter = 10+20+30 i.e. 60
Half Year = 10+20+30+40+50+60 i.e. 210
May I know what are changes required in calculating Amount measure/expression?
Any help will be highly appreciated.
Regards,
Vinesh
Hi Vinesh,
you need to create multiple columnsfor each 1st month ,Quarter and Half year or still you want single column you need to duplicate the rows like below ,
Month | Amount | Month Bucke |
Jan | 10 | 1st Month |
Jan | 10 | Quarter |
Feb | 20 | Quarter |
Mar | 30 | Quarter |
Jan | 10 | Half Year |
Feb | 20 | Half Year |
Mar | 30 | Half Year |
Apr | 40 | Half Year |
May | 50 | Half Year |
Jun | 60 | Half Year |