topic Re: Help with IF condition for field total in QlikView

Help with IF condition for field total

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I will like to calculate the total excess column using the following condition

if sum of currentstock - forecast > 0, excess is currentstock - forecast, else its zero.

As u can see the sum of excess is 85 which is less than the forecast, i want to compare their totals and if excess is less than forecast, i should get a zero as the forecast will eat up the excess.

if the total excess is bigger than the forecast, then the excess with multiply the corresponding cost to give excess dollar. I know its a lot but I will really appreciate your input.

Re: Help with IF condition for field total

vishsaggi — Tue, 04 Sep 2018 19:55:53 GMT

Can you share some sample data as text rather than image. And your expected output you would want to see?

Re: Help with IF condition for field total

Anonymous — Tue, 04 Sep 2018 20:12:49 GMT

I have attached a simple excel that explains what i want to do. Let me know if that clears it up

Thanks

Re: Help with IF condition for field total

neelamsaroha157 — Tue, 04 Sep 2018 20:36:30 GMT

Is this what you are looking for -

Re: Help with IF condition for field total

vishsaggi — Tue, 04 Sep 2018 20:42:22 GMT

is this what you are looking for? If yes, then add straight table with following dimensions and expression accordingly.

Dim:

Product

StoreLocation

Forecast

Excess

expr:

= IF(Sum(TOTAL <Product> CurrentStock) > Sum(TOTAL <Product> Forecast), 0, Sum(TOTAL <StoreLocation> CurrentStock) - Sum(TOTAL <Product> Forecast))

= Sum(Cost)

= Num(fabs(Column(1) * Sum(Cost)), '$'&'(#,##0.00)')

Change the labels for expressions accordingly.

Re: Help with IF condition for field total

Anonymous — Tue, 04 Sep 2018 21:20:11 GMT

thanks for the response, the excess column is returning 0 for everything, i tried it on my dataset and it returns zero all through

Re: Help with IF condition for field total

Anonymous — Tue, 04 Sep 2018 21:21:14 GMT

thanks a lot for your response, i tried your solution on my dataset and the excess column returned same value as the currentstock

Re: Help with IF condition for field total

vishsaggi — Tue, 04 Sep 2018 21:29:36 GMT

Can you try this for excess? If not can you send us the screen shot where you can see the 0's coming

= IF(Sum(TOTAL <Product> CurrentStock) > Sum(TOTAL <Product> Forecast), 0, Sum(TOTAL <StoreLocation, Product> CurrentStock) - Sum(TOTAL <Product> Forecast))

Re: Help with IF condition for field total

Anonymous — Tue, 04 Sep 2018 21:46:00 GMT

so i had to blur off some parts but its the same as i showed earlier, the material is the product.

Re: Help with IF condition for field total

vishsaggi — Tue, 04 Sep 2018 21:51:27 GMT

‌you have lot of other dimensions here we have to look into your actual data set to come up with correct expression. Based on what you sent in your excel it works but if your dataset is different we might need to tweak around. Can you share your qvw file masking data. You can scramble your data using scramble technique. Check here

Preparing examples for Upload - Reduction and Data Scrambling