topic Re: Sum and Avg in QlikView

Sum and Avg

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have fields ID,Date,Sales,Cost,Final , i want

Sum , Avg and STDEV of Sales for every ID , if sales has leading zeros in the calculation they should be ignore and consider the data points till previous to previous month [Jun]

please find the attachment for the sample data and expected output.

Thanks

John

Re: Sum and Avg

ToniKautto — Tue, 30 Aug 2016 08:32:49 GMT

The community in general is not intended free consulting, the focus is more to help and support each other when we get stuck on problems.

Your current statement does not have any clear problem described, or even a questions mark. Can you please elaborate on where you got stuck when trying to accomplish your requirements? Are you having problems loading the data, creating a sheet object or setting up the aggregation? Can you please attach a QVW of how far you got and where you are stuck?

Re: Sum and Avg

Tue, 30 Aug 2016 09:27:26 GMT

UPs, sorry every one,

I have loaded the data and i have created a straight table with Date as dimension, now i tried to calculate the Sum(Sales) . if sales has leading zeros, in the calculation they should be ignore and i need to consider the data points till previous to previous month [Jun], for this i am trying to write an expression with set analysis but i am not getting correct results.

Expression:

Sum({<Sales={'>0'}>}Sales)

Please help me on this expression. how to include the above highlighted conditions in the Set analysis.

Thanks

John.

Re: Sum and Avg

varshavig12 — Tue, 30 Aug 2016 09:33:10 GMT

Try this,

=sum({<YM={"<=$(=Date(today(),'YYYY')&Date(AddMonths(Today(),-2),'MM'))"}>}Sales)

Here, YM = Year&Month

eg: 201606