topic Re: Dimension/Expression in QlikView

Dimension/Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I would like to get the result as below. I tried to set up three Dimensions: Submitted_status, Sumitted_year and Submitted_month, and two Expressions: Submission total and % on time. All these should also match when the submitted_company = 'A'.

So I worte the code for "%on time" shows as below,

if ((Submitted_Status) = 'ontime',
((Sum ({$<Submitted_Status = {"time"}, submit_company = {'A'}>} Submission_count))
/(Sum( {$<submit_company = {'A'}>} Submission_Count))),
((Sum ({$<Submitted_Status = {"Submitted late"},submit_company = {'A'}>} Submission_count))
/(Sum( {$<submit_company = {'A'}>} Submission_Count)))
)

However, I cannot make the "%on time" expression work. Can anyone please let me know or provide me any hints that how to get my expressions with the current dimensions?

Thanks a lot!

Becky

Re: Dimension/Expression

Tue, 26 Jul 2016 18:26:21 GMT

use this expression for %

=sum(Submission_count)/ Sum(TOTAL <submitted_year,submitted_month> Submission_count)

Re: Dimension/Expression

Tue, 26 Jul 2016 19:08:11 GMT

Thanks Rodrigo. I just changed my code. It's slightly different. So the one you posted didn't work. Do you mind to take a look at it again?

Re: Dimension/Expression

Tue, 26 Jul 2016 19:20:07 GMT

Do you want only on time? or

i used the same expression ....

Re: Dimension/Expression

Tue, 26 Jul 2016 19:28:02 GMT

Thank you rodrigo!

It's werid, I got the result on this sample qvw. But I cannot get it from my work qvw file.

Can you let me know why we are using TOTAL here and TOTAL <submitted_year,submitted_month>?

Re: Dimension/Expression

Tue, 26 Jul 2016 19:31:59 GMT

the total is a function that adds the data ( a grand total ) and when used in a field , we add the value for that field

Re: Dimension/Expression

vinieme12 — Tue, 26 Jul 2016 19:32:31 GMT

This is all you need

= Sum({<submit_company={'A'}>}Submission_count)/

Sum(TOTAL <submitted_year,submitted_month> {<submit_company={'A'}>}Submission_count)

See app attached

Re: Dimension/Expression

Tue, 26 Jul 2016 19:39:00 GMT

Thanks so much! I am all set now. I got the result! It was a typo.

Best,

Becky

Re: Dimension/Expression

Tue, 26 Jul 2016 19:42:58 GMT

Sorry rodrigo. I marked Vineeth's response as correct answer. As I have a condition in my requirement which is the submitted_company have to equal to 'A'. So he's answer is the correct one. Without adding the condition, the result is different. I really appreaciate your help and pacient.

Re: Dimension/Expression

Tue, 26 Jul 2016 19:45:01 GMT

no problem. the important thing is to solve your problem the best way.

Re: Dimension/Expression

Tue, 26 Jul 2016 19:46:32 GMT

Thanks so much Vineeth. Problem sovled!

Best,

Becky

Re: Dimension/Expression

Tue, 26 Jul 2016 19:58:25 GMT

Thanks!

Re: Dimension/Expression

vinieme12 — Tue, 26 Jul 2016 20:00:43 GMT

Glad to help

Cheers