Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Front end expression

Hello,

I do have data like:

LOAD * INLINE [

    Date, Type, Price, ID

    2015-01-01, P, 8, 1

    2015-01-01, F, 10, 1

    2015-01-03, P, 2, 1

    2015-01-03, F, 2, 1

   

    2015-01-01, P, 8, 2

    2015-01-01, F, 10, 2

    2015-01-03, P, 2, 2

    2015-01-03, F, 2, 2

];


The logic

Field Type stands for Plan (P) and Fact (F).

If F/P > 1.1 than 1 else 0 aggregated according to ID and Date.

The temporary table might look like this:

Date          ID Result

2015-01-01 1   1

2015-01-03 1   0

2015-01-01 2   1

2015-01-03 2   0


My goal is to get pivot table that shows the results like:

Date          Result

2015-01-01 2

2015-01-03 0

Please help my out with this issue. All calculations should be made in front end.

Thank you!

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

use below, and uncheck suppress zero values

sum(AGGR(if((sum({<Type={'F'}>} Price)/sum({<Type={'P'}>} Price))>1.1,1,0),Date,ID))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

2 Replies
vinieme12
Champion III
Champion III

use below, and uncheck suppress zero values

sum(AGGR(if((sum({<Type={'F'}>} Price)/sum({<Type={'P'}>} Price))>1.1,1,0),Date,ID))

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Frank_Hartmann
Master II
Master II

see attached file,

hope this helps