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: 
Karahs
Partner - Creator
Partner - Creator

Help in finding out formula!

Hello everyone,

          Finding difficulty in calculating formula:

I Have four tables and their fields as metioned below:

1) MISGrpMaster:

  • MISGrpID,
  • MISGrpName,
  • DispOrder

2) MISSubGrpMaster:

  • MISSubGrpID,
  • MISGrpID,
  • MISSubGrpName,
  • DispOrder

3)MISParticularsMaster:

  • MisParticularsID,
  • MisSubGrpID,
  • SunAcctCode,
  • MisParticularDetail

4)MISBasicTransaction:

  • TransID,
  • SunAcctCode,
  • AccountingPeriod,
  • TransactionAmt

And here is my code as follows:

LIB CONNECT TO 'TempBI ()';

LOAD MISGrpID,

    MISGrpName,

    DispOrder as GrpDispOrder;

SQL SELECT MISGrpID,

    MISGrpName,

    DispOrder

FROM "WSG_BI".dbo.MISGrpMaster;

LIB CONNECT TO 'TempBI ()';

LOAD MISSubGrpID,

    MISGrpID,

    MISSubGrpName,

    DispOrder as SubGrpDispOrder;

SQL SELECT MISSubGrpID,

    MISGrpID,

    MISSubGrpName,

    DispOrder

FROM "WSG_BI".dbo.MISSubGrpMaster;

LIB CONNECT TO 'TempBI ()';

LOAD MisParticularsID,

    MisSubGrpID,

    SunAcctCode,

    MisParticularDetail;

SQL SELECT MisParticularsID,

    MisSubGrpID,

    SunAcctCode,

    MisParticularDetail

FROM "WSG_BI".dbo.MISParticularsMaster;

LIB CONNECT TO 'TempBI ()';

LOAD TransID,

    SunAcctCode,

    AccountingPeriod,

    left(AccountingPeriod,4) as APYear,

    right(AccountingPeriod,3) as APMonthId,

    TransactionAmt;

SQL SELECT TransID,

    SunAcctCode,

    AccountingPeriod,

    TransactionAmt

FROM "WSG_BI".dbo.MISBasicTransaction;

Month_Name:

Load  *  inline [ APMonthId,APMonthName,Monthorder

  001,Apr,1

            002,May,2

            003,Jun,3

            004,Jul,4

            005,Aug,5

            006,Sep,6

            007,Oct,7

            008,Nov,8

            009,Dec,9

            010,Jan,10

            011,Feb,11

            012,Mar,12

      ];

SO MY QUESTION IS THAT I WANT TO CALCULATE SUM OF TRANSACTION OF THE BELOW ATTACHED IMAGE FIELDS:

op.png

SUM OF TRANSACTION OF ALL THE MISSubGrpName fields where MISSubGrpName is from MISGrpName...

I hope you 'll understand my question..I wanna know how to calculate this query

Thanks in advance.

Regards

1 Reply
Gysbert_Wassenaar

Simply select a MISGrpName value and calculate the results with sum(TransactionAmt)


talk is cheap, supply exceeds demand