topic Re: How to add records of same type in a chart expression? in QlikView

How to add records of same type in a chart expression?

trishita — Wed, 25 Nov 2020 16:16:04 GMT

if (Match([SC_FUEL_TYPE],'DMA'), sum(num(FUEL_MASS_BEFORE_SOUNDING/ 1000, ' ####.###', '.', ',' )),

if (Match([SC_FUEL_TYPE],'DMB'), sum(num(FUEL_MASS_BEFORE_SOUNDING/ 1000, ' ####.###', '.', ',' )),

if (Match([SC_FUEL_TYPE],'RMK 500'), sum(num(FUEL_MASS_BEFORE_SOUNDING/ 1000, ' ####.###', '.', ',' )),

if (Match([SC_FUEL_TYPE],'RMG 500'), sum(num(FUEL_MASS_BEFORE_SOUNDING/ 1000, ' ####.###', '.', ',' )),'Unknown'))))

I used this code, but still its not working.Could anyone give a try?

I want to see the sum of individual fuel oil like sum of DMA, or RMD 80 or RMK 700

Should I use totat or aggregate function?

Re: How to add records of same type in a chart expression?

sibin_jacob — Fri, 03 Aug 2018 12:04:24 GMT

Please try the below expression.

I have used TOTAL Keyword.

below expression included only for DMA, the same way you can add for the other values.

if (Match([SC_FUEL_TYPE],'DMA'), sum({<[SC_FUEL_TYPE]={'DMA'}>} Total FUEL_MASS_BEFORE_SOUNDING))

Thanks,

Sibin

Re: How to add records of same type in a chart expression?

trishita — Fri, 03 Aug 2018 12:32:47 GMT

if (Match([Actual_Fuel_Type],'HSFO'), sum( total {$<Actual_Fuel_Type={'HSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),

if (Match([Actual_Fuel_Type],'LSDO'), sum( total {$<Actual_Fuel_Type={'LSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),

if (Match([Actual_Fuel_Type],'HSDO'), sum( total {$<Actual_Fuel_Type={'HSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),

if (Match([Actual_Fuel_Type],'ULSFO'),sum( total {$<Actual_Fuel_Type={'ULSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),'Unknown'))))

if (Match([SC_FUEL_TYPE],'RMG 500'), sum( total {$<SC_FUEL_TYPE={'RMD 500'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),

if (Match([SC_FUEL_TYPE],'DMA'), sum( total {$<SC_FUEL_TYPE={'DMA'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),

if (Match([SC_FUEL_TYPE],'DMB'), sum( total {$<SC_FUEL_TYPE={'DMB'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000)),'Unknown')))))

if (Match([SC_FUEL_TYPE],'RMD 80'),sum(total{$<SC_FUEL_TYPE={'RMD 80'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([SC_FUEL_TYPE],'RMK 700'),sum( total{$<SC_FUEL_TYPE={RMD 80}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([SC_FUEL_TYPE],'RMG 500'), sum( total{$<SC_FUEL_TYPE={DMA}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),'Unknown'))))))

When I am trying to do individually its working but when I am trying to use for multiple ifs this syntax is showing an error..

GARBAGE AFTER EXPRESSION ','

Re: How to add records of same type in a chart expression?

sibin_jacob — Fri, 03 Aug 2018 12:38:19 GMT

I think it is because of syntax error,

You need to check the brackets and comma.

Re: How to add records of same type in a chart expression?

sibin_jacob — Fri, 03 Aug 2018 12:42:01 GMT

Please try this one.

if (Match([Actual_Fuel_Type],'HSFO'), sum( total {$<Actual_Fuel_Type={'HSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'LSDO'), sum( total {$<Actual_Fuel_Type={'LSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'HSDO'), sum( total {$<Actual_Fuel_Type={'HSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'ULSFO'),sum( total {$<Actual_Fuel_Type={'ULSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),'Unknown'

))))

Re: How to add records of same type in a chart expression?

neelamsaroha157 — Fri, 03 Aug 2018 12:51:37 GMT

Hi Trisha, Is there a reason that you are using all the Fuel types in different match statement?

If no, the may be you can use -

if (Match([SC_FUEL_TYPE],'DMA', 'DMB', 'RMK 500', 'RMG 500'), sum(num(FUEL_MASS_BEFORE_SOUNDING/ 1000, ' ####.###', '.', ',' )),

,'Unknown')

Re: How to add records of same type in a chart expression?

neelamsaroha157 — Fri, 03 Aug 2018 12:53:49 GMT

Or You can use set analysis as -

Sum({<[SC_FUEL_TYPE] = {'DMA', 'DMB', 'RMK 500', 'RMG 500'}>}FUEL_MASS_BEFORE_SOUNDING)/1000

Re: How to add records of same type in a chart expression?

trishita — Fri, 03 Aug 2018 12:55:58 GMT

No , but i want summed up values for this respesctive fuel type...i am new to qlikview and dont know so much..I tried to write the conditional statement

Sum of all fuel type DMA ;

SUM OF ALL FUEL TYPE DMB

Re: How to add records of same type in a chart expression?

neelamsaroha157 — Fri, 03 Aug 2018 12:57:42 GMT

Did you try the set analysis provided above?

Re: How to add records of same type in a chart expression?

trishita — Fri, 03 Aug 2018 13:03:55 GMT

The syntax is working fine..Only Now the records are duplicated.How can I fetch distinct records...

if (Match([Actual_Fuel_Type],'HSFO'), sum( total {$<Actual_Fuel_Type={'HSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'LSDO'), sum( total {$<Actual_Fuel_Type={'LSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'HSDO'), sum( total {$<Actual_Fuel_Type={'HSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'ULSFO'),sum( total {$<Actual_Fuel_Type={'ULSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),'Unknown'

))))

Re: How to add records of same type in a chart expression?

trishita — Fri, 03 Aug 2018 13:27:10 GMT

Its working now.I removed some hidden dikmensions

Re: How to add records of same type in a chart expression?

trishita — Thu, 09 Aug 2018 11:46:06 GMT

Is it possible to also add records dependent on SOUNDING_DATE? Like what if i want to sum up the values of Computed ROB and Actual ROB dependent on a date and survey id

if (Match([Actual_Fuel_Type],'HSFO'), sum( total {$<Actual_Fuel_Type={'HSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'LSDO'), sum( total {$<Actual_Fuel_Type={'LSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'HSDO'), sum( total {$<Actual_Fuel_Type={'HSDO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),

if (Match([Actual_Fuel_Type],'ULSFO'),sum( total {$<Actual_Fuel_Type={'ULSFO'}>}FUEL_MASS_BEFORE_SOUNDING/ 1000),'Unknown'

))))