Count Text for KPI Calculation

[Yuva]

Hello Everyone,

I am new user / Developer of Qlik sense

I am facing problem while counting the Text strings , Please refer an attachment to know more about my data.

KPI Field is showing final output in attachment. KPI Calculation conditions mention below. 

ex. if AA customer have 'Ext' flag for one month and remaining 11 months are no flags then that case is 'One-off'
if AA have flag atleast for two seperate month then case is 'Repeat'
if any one of have multiple flag in single month and remaining all months no flags then case is 'Single month'
if any one have flag at least five times out of last 6 months then its 'Regular'

Waiting for your valuable reply Guys.

Thanks.

[sunny_talwar]

You want to do this on the front end of the app? Try this expression with Customer Name as dimension

=If(Count(Flag) = 1, 'One off',
	If(Count(DISTINCT Month) = 1, 'Single Month',
    	If(Count(DISTINCT Month) >= 5, 'Regular', 'Repeat')))

[Yuva]

Hi Sunny,

Thanks for the efforts.

But my second table given for only explanation purpose . In the first table (Attachment) KPI field is the output field which is expected output.

My Customer name (dimension) is not unique. Customer records repeated in the data .

Regards,

Yuva

[sunny_talwar]

Are you saying that you want to do this in the script?

[Yuva]

Hi Sunny,
Actually at back-end my data associated with multiple tables and without affecting this....can we do this at front end by expression.
My data is incremental and till the time I have 2018 ......12 months data. Also sample dummy data which I have shared it contains the fields only which needed.

Regards
Yuva

[sunny_talwar]

The expression I gave is a front end expression... is that not working for you?

[Yuva · Accepted solution]

Hello,
Let me check again with that Expression.
Will back to you soon.

Regards
Yuva

[Yuva]

Thanks Sunny.......!
Victory ....for me.
Your logic with customer dimension working fine and showing exact result what I want.
Thank you So much.

Regards,
Yuva