Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean: Not applicable

## Total in staright table

Hi

I have the below straight table

The formula for the total column is  (days*volume*(0.3))+(days*volume)/150  = total

 employee id days volume total a 1 27 0.020116677 0.004707302 a 2 3 0.024473813 0.000636319 a 3 94 0.02513826 0.020479303

My output should be as below

 employee total a 0.025822924

i'm using the below formula i'm getting correct answer if i'm adding the ID field, but i dont want to show ID field and a unique employee

= sum(days* volume*0.3)+ sum(days)*sum( distinct volume) /150

1 Solution

Accepted Solutions MVP

Try this:

=Sum(Aggr(Sum(days* volume*0.3) + Sum(days) * Sum(DISTINCT volume) /150, employee, id))

9 Replies MVP

Try this:

=Sum(Aggr(Sum(days* volume*0.3) + Sum(days) * Sum(DISTINCT volume) /150, employee, id)) Not applicable
Author

Hi Sunny its giving me very high value when compared to the actual value

its giving me 23.050 but the value is 0.65143....

employeeiddaysvolumetotal
a19830.764653784219

 0.651434 MVP

Would you be able to share a sample where this isn't working? Creator

Hi John, are you pre-calculating the total amount in your load script?

If not, I would suggest that you consider moving this calculation to the script.

If you're wanting to calculate it in a chart, then I would imagine an expression like the one below would suffice:

sum(((days*volume*0.3)+(days*volume))/150) S. MVP

Try to create one variable for 0.3 like

LET Vstatic = num('0.3','##.##0')

Then use this variable in to that expression

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context) Creator III

Hi John,

Try this..

=Sum(Aggr((Sum(days* volume*0.3) + Sum(days) * Sum(DISTINCT volume)) /150,Employee,Id)) Specialist

Sunny T's approach would work if the data was just those 3 rows. I suspect that this is not the raw data (the DISTINCT is the main clue).

If my suspicion is correct, please describe or show us the raw data Not applicable
Author

Hi sunny tanks a lot and the problem is resolved now Not applicable
Author
{"ft_lang_label":"__label__en","ft_lang_prob":0.80763024,"math_prob":0.95022607,"size":1810,"snap":"2023-40-2023-50","text_gpt3_token_len":566,"char_repetition_ratio":0.1290144,"word_repetition_ratio":0.07986111,"special_character_ratio":0.34198895,"punctuation_ratio":0.12144703,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9914792,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1,null,null,null,null,null,null,null,null,null,1,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2023-10-03T07:15:10Z\",\"WARC-Record-ID\":\"<urn:uuid:c5249f0d-753f-4017-94e6-ce8032a34e92>\",\"Content-Length\":\"422168\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:da68105b-1236-484e-b6e6-b3bfffae2d25>\",\"WARC-Concurrent-To\":\"<urn:uuid:884dbd3f-496c-4d8b-a8f1-ea972aa3a93b>\",\"WARC-IP-Address\":\"108.157.214.87\",\"WARC-Target-URI\":\"https://community.qlik.com/t5/QlikView-App-Dev/Total-in-staright-table/m-p/1191542\",\"WARC-Payload-Digest\":\"sha1:H5ULYSTE725LKKIRFTGZPB3NYZGGGX5H\",\"WARC-Block-Digest\":\"sha1:B5XWCIAYIYZGRPWHJQ7ULZAPRZWAJ5PU\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2023/CC-MAIN-2023-40/CC-MAIN-2023-40_segments_1695233511055.59_warc_CC-MAIN-20231003060619-20231003090619-00756.warc.gz\"}"}