Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akpofureenughwu
Creator III
Creator III

How to interswitch dimension and measure

Hello there,

I have a set of data in this format.

CompanyYEARMONTHLTIUACOSNMFI
JohnLee2016January1561243
BarryHouses2016October04044
Choi and Sons2016December1340451

after the loading the data into my qliksense app, the data looks like this:

LOAD *,

if( Isnum(Date), 'Q')& Ceil(Month(Date)/3) as Quarter;

LOAD *,

    

    Date(Date#(Year&Month, 'YYYYMMMM')) as Date;

LOAD Company

        "YEAR"as Year,

    "MONTH" as Month,

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

XXXXXXXXXX

With this, I can easily build a visualization, using Company, Year, Quarter  and Month  as dimension and LTI, UAC, OS as measures.(picture 1)

The clients wants the reverse (LTI, UAC, OS as dimension and Quarter measure) (picture HSI)

See attached for clarification

12 Replies
shansundar
Partner - Creator
Partner - Creator

You can check whether this query also. Its working for me for the sample data. Using the result set generated by this query you can create your desired chart.

temp:

CrossTable(Dim, value, 3)

LOAD Company,

    YEAR,

    MONTH,

    LTI,

    UAC,

    OS,

    NM,

    FI

FROM

//change this to your lib path and table name.

(ooxml, embedded labels, table is Sheet1);

final:

NoConcatenate

LOAD

Company,

'Q' & ceil(month(Date)/3) as Quarter_number,

Dim,

value;

LOAD

Company,

Date(Date#(YEAR&MONTH, 'YYYYMMMM')) as Date,

Dim,

value

RESIDENT temp;

DROP TABLE temp;

Thanks,

Shan S

akpofureenughwu
Creator III
Creator III
Author

Hello Subbiah,

I confirmed that I modified the script and the lib path...

It's working now Thanks

akpofureenughwu
Creator III
Creator III
Author

stalwar1    I have modified the lib path .. Thanks to shansundar

This is the existing script

LIB CONNECT TO 'HSE';

[GAB]:

CrossTable (Dim, Value, 6)

LOAD ASSET ,

    "FIELD" ,

    "YEAR",

    "MONTH" ,

    Date(Date#(YEAR&MONTH, 'YYYYMMMM')) as Date,

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

    OS,

    "DO",

    CD,

    MTC;

SQL SELECT ASSET,

    "FIELD",

    "YEAR",

    "MONTH",

    FAT,

    LTI,

    RTA,

    UA,

    UC,

    NM,

    OS,

    "DO",

    CD,

    MTC

FROM "HSE".dbo."INCIDENTS REPORT HSE";

FinalTable:

NoConcatenate

  

LOAD ASSET as Asset,

    "FIELD" as Field,

    "YEAR"as Year,

    "MONTH" as Month,

    Date,

    Value

Resident GAB ;

DROP Table GAB;

But my challenge is the values of LTI, FAT and others are combined and not distinct.... See attached (value1)

can't the design  in HSI still achievable?

Regards