topic How to perform a calculation without using sum in QlikView

How to perform a calculation without using sum

Thu, 17 Jul 2014 14:31:14 GMT

Hi,

In my QVW one of the fields named sales has a value which is correct for each week if i take it in a text box, if i use a text object and do SUM(Sales) what happens is instead of calculating the sales for the given filters its calculating the sales for everything because the sales is already aggregated before coming into this QVW.

If i pick a certain date and in the text object if i do Sales i get the exact value but if i do sum(sales) it gives me everything.

The problem with this is, if i pick multiple dates the text box gets blanked out as i am not doing any aggregation operation.

I could have posted my QVW but its too large.

Can someone please throw any suggestions on this?

Thanks.

Re: How to perform a calculation without using sum

Anonymous — Thu, 17 Jul 2014 14:33:53 GMT

Hi,

Can you use function only

Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 14:59:51 GMT

Hi Nita,

The problem with only function is it works fine if i just select one date , but if i select multiple dates then it returns blank same as just the sales.

Any suggestions?

Thanks

Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:02:04 GMT

Would it be possible for you to post the QVW with only a sample amount of data so we could get an idea of the behavior you're describing?

Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 15:10:33 GMT

If I understand you correctly, you have a non-additive number? If you pick any date, you will get a relevant number. But you can't sum the number over several dates.

If so, you should use some other aggregation function than Sum(). Suggestions:

- Avg(Aggr(Sum(Sales),Date)) = Sum(Sales) / Count(distinct Date)

This sums within each date, and averages over dates.

- FirstSortedValue(Aggr(Sum(Sales),Date),-Date)

This sums within each date, and picks the last date.

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:12:02 GMT

Hi,

I have attached the reduced version and if you seee the blue text object i have my script in it and it works fine for 1 date but if i select multiple dates it gets blanked out.

Can you please look into it and let me know any suggestions.

Thanks

Re: How to perform a calculation without using sum

Anonymous — Thu, 17 Jul 2014 15:12:16 GMT

ok.

Can you provide some details. It will be really nice if you can reproduce the issue in a .qvw

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:15:38 GMT

Hi Nita,

I have attached my QVW below which has the issue.

Thanks

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:16:02 GMT

Hi Henric,

I tried both of your conditions and nothing worked out for me.

Thanks

Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 15:41:34 GMT

My point is that you cannot use Only(). Then you will get exactly this behaviour.

I don't know exactly how you want to perform your calculation, but both the following expressions give results that look OK:

=Avg({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Transaction Count])

=FirstSortedValue({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Transaction Count], [Date Id])

But, since you have several transactions per date, I suspect you need an Aggr() function. How should such cases be handled? Sum within a day and then average over the different days, or?

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:47:11 GMT

Hi Henric,

Yes you are right i have multiple transactions per day and each location has 1 transaction so, if i pick multiple dates and say one location then the transaction count must be the sum of that location for all the dates selected.

I hope this is clear , if not please let me know i will try to explain more clearly.

Thanks

Re: Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 15:53:04 GMT

So, if I select 1 location and many dates, should I always sum the number? Or is there a limit, per week, or so?

And if I select 1 date and many locations, should I never sum the number? What should I do instead? Average?

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 15:56:04 GMT

Basically the way the QVW is designed is the date we see it has the sales and everything for the whole week aggregated to that date.

So, lets say we select multiple dates and multiple locations then we must get the sum.

For example if i select 2 dates in a month and 1 location then i must get the sum of tran count for those 2 dates in that location.

Thanks

Re: Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 16:08:39 GMT

So, the dates represent weeks and are additive? E.g. if I have one location and two dates, the [Transaction Count] should be summed.

And If I have one date and two locations, the [Transaction Count] should also be summed.

Then it sounds to me as if the [Transaction Count] is additive both over the time dimension and the location dimension? So you can use Sum([Transaction Count]) straight off? Or what am I missing?

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 16:15:08 GMT

if i use the sum(transaction count) i see a huge number popping up..if you can see in the text box we can see that i am not sure why?

Re: Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 17:11:07 GMT

And what makes you think that this number is wrong? You have 233 locations of which many have a total of more than a couple of thousands.

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 17:14:02 GMT

if i pick 2 dates and one location i see 2 values in the transaction count text box and i want to see the sum of those 2 in the text object but i dont see that,And i think there is some problem with my load script.Can you take a look at it and let me know.

Thanks

Re: Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 18:13:34 GMT

Two dates and one location. In the case below, you have 256 and 257 as transaction counts. Is 483 the number you want?

HIC

Re: Re: How to perform a calculation without using sum

Thu, 17 Jul 2014 18:15:39 GMT

Yep exactly, may i know how did you achieve it?

Thanks

Re: Re: How to perform a calculation without using sum

hic — Thu, 17 Jul 2014 18:18:57 GMT

The chart uses

=Sum([Transaction Count])

and the blue text box uses

=Sum({<TransactionChannelDesc ={POS},TransactionTypeId = {1}>}[Transaction Count])

HIC