Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
wenjun2016
New Contributor II

Checking values across months

Hi,

Scenario 1:

Can anyone suggest a best dynamic method to check the values across existing data month columns. (data may not have full 12 months)

My data format is as below, one month's value is recorded in one column.

I need to flap those customer having  consistent value of 10 throughout the year.

CustomerJan2016Feb2016Mar2016Apr2016May2016Jun2016Jul2016Aug2016Sept2016Oct2016Nov2016Dec2016
A101010101010101010101010
B7834323223312123417345689
C101010101010101010101010

Resulting new column : Flap

CustomerFlap
A1
B0
C1

Scenario 2:

If my data structure changed to :

CustomerValuesMonthYear
A10

Jan2016

A10Feb2016
A10Mar2016
A10Apr2016
A10May2016
A10Jun2016
A10Jul2016
A10Aug2016
A10Sept2016
A10Oct2016
A10Nov2016
A10Dec2016
B78Jan2016
B343Feb2016
B23Mar2016
B22Apr2016
B33May2016
B12Jun2016

How to check that the value is 10 consistently throughout current number of months (may not have 12 months data) ?

A new column Flap is also generated.

Thank you for your help!

3 Replies
MVP & Luminary
MVP & Luminary

Re: Checking values across months

I would transform the source per The Crosstable Load into a normal data-structure like your scenario two and you could add per left join an aggregation-load with count(YearMonth) on Customer and a where-clause on the Values which aren't NULL of these data to get the number of months with Values for each Customer - and you might take this result to compare it against your expected result to flag them.

If you really want to check that each monthly Value is higher as certain threshold than you will need to use interrecord-functions within a sorted resident-load to check the values of the previous loaded records - for this see here: Peek() or Previous() ?

- Marcus

wenjun2016
New Contributor II

Re: Checking values across months

Hi Marcus,

I have tried to use Crosstable to turn into scenario 2, I create a dimension Flap, but my expression returns 1 whenever the customer account has a value 10 in any of the months.

if(Values = 10.0, 1, 0)  how to add on the part to check for every months?

From your suggestion above, can I understand that I should count(MonthYear), say = 12.

so I will

aggr( count(if(Values = 10.0, Values)), Customer)

If the count result is also 12, then it will be flapped.

Thank you!

polestar_suppor
New Contributor

Re: Checking values across months

Suppose the data is in the "data_table" in the form of Scenario 2.

The below code first creates a new column "value_check" which has 1 in a row if all the "Values" are 10 for a Customer till that row. Then for every Customer we check the last Value. I am assuming the data is ordered by Customer, MonthYear.

This way you get a new table with Customers and the "flap" value.

data_table_2:

load

  *,

    if(peek(Customer)=Customer and peek(Values)=Values

    and Values=10 and peek(value_check)<>0,1,

        if(peek(Customer)<>Customer and Values=10,1,0)) as value_check

resident data_table;

Flap_table:

Load

  Customer,

    LastValue(value_check) as flap

resident data_table_2

group by Customer;

Community Browser