Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

dsharmaqv
Contributor III

Month Over Month Comparison of string Fields

Hi

I have a requirement to compare string fields over the months for any change in the string value.

For Example :

I have to compare the status of countries and have to color the background if there ia any change in status.

For india status is "Hign" in May and changed to "Low" in June so I have highlight the change.

Month              Country               Status

--------------------------------------------------------

May                 India                    High

June                India                    Low

July                 India                    Low

May                 Japan                  Low

June               Japan                   Low

July                Japan                   Low

Can some one help me out . This is urgent!!

1 Solution

Accepted Solutions

Re: Month Over Month Comparison of string Fields

Like this?

Capture.PNG

Used the following script to create a flag:

Table:

LOAD Month(Date#(Month, 'MMMM')) as Month,

  Country,

  Status;

LOAD * INLINE [

    Month,              Country,              Status

    May,                India,                    High

    June,                India,                    Low

    July,                India,                    Low

    May,                Japan,                  Low

    June,              Japan,                  Low

    July,                Japan,                  Low

];

FinalTable:

LOAD *,

  If(Country = Previous(Country) and Status <> Previous(Status), 1, 0) as Flag

Resident Table

Order By Country, Month;

DROP Table Table;

and then used the following background expression:

=If(Flag = 1, LightRed())

5 Replies

Re: Month Over Month Comparison of string Fields

Like this?

Capture.PNG

Used the following script to create a flag:

Table:

LOAD Month(Date#(Month, 'MMMM')) as Month,

  Country,

  Status;

LOAD * INLINE [

    Month,              Country,              Status

    May,                India,                    High

    June,                India,                    Low

    July,                India,                    Low

    May,                Japan,                  Low

    June,              Japan,                  Low

    July,                Japan,                  Low

];

FinalTable:

LOAD *,

  If(Country = Previous(Country) and Status <> Previous(Status), 1, 0) as Flag

Resident Table

Order By Country, Month;

DROP Table Table;

and then used the following background expression:

=If(Flag = 1, LightRed())

dsharmaqv
Contributor III

Re: Month Over Month Comparison of string Fields

Thanks Sunny

I tried it ...and it works. need to validate with actual data.

Also for month over comparesion do I need to add Month in condition to populate flag.

I dont know if I am asking silly question. but want to understand how Previous function works.

Regards

Re: Month Over Month Comparison of string Fields

As long as you have data for all Months you don't really need to check for Month because I would hope them to be incremental. But if there is a case that the data might be missing for some months and you want a different flag when that is the case, you might have to use Month in the condition as well. It all depends on your what you need.

dsharmaqv
Contributor III

Re: Month Over Month Comparison of string Fields

Hi Sunny

I was working on your solution and observed ...it is working fine for straight table but not for Pivot table.

Please assist

Re: Month Over Month Comparison of string Fields

How is the Pivot Table structured? May be this?

Capture.PNG

I see that different positioning of dimensions lead to different results, so it would be best if you can open the file and adjust the pivot table to your need and reattach it.

Community Browser