how to get totals per month and per user?

[rongame]

hi

how do i display the total sales per month from an element say"empenc_warranty___purchased_amount" but considers the logged in user and the current month?

thanks guys.

 

[cheesegrits]

Isn't this the issue we created a view for on your 'netpod' domain?

-- hugh

 

[rongame]

its smething similar. my problem is that the code below will generate all the months. i just want to display the actual month and perform a liitle bit of calculation to get the remaining sales quota. i can add {$my->id} to filter out the results for the logged in user. but how can i display if the admin whats to see results for a particular seller?

i created an element named
thismonths_salesquota for what it says - admin will enter data
yoursales_totalmonthly for the total sales for this month - can we get this from the view?
balance for checking if how much more needs to be sold - this i thinks can be done with basic calc

thanks

DROP VIEW IF EXISTS hughtest; CREATE VIEW hughtest AS SELECT id, DATE_FORMAT( date_time, '%Y-%m-%d' ) AS datetime, encoder AS user_id, AVG( purchased_amount ) AS average, SUM( purchased_amount ) AS sum, COUNT( purchased_amount ) AS no_of_entries, SUM(CASE WHEN product_clas = 0 then 1 Else 0 end) AS aircon, SUM(CASE WHEN product_clas = 1 then 1 Else 0 end) AS refrigerator,SUM(CASE WHEN product_clas = 2 then 1 Else 0 end) AS washing_machine,
SUM(CASE WHEN product_clas = 3 then 1 Else 0 end) LED_TV
FROM empenc_warranty GROUP BY datetime, user_id

 

[cheesegrits]

Well you should be able to handle the month filtering with a pre-filter. We have a built in "this month" per-filter.

Not sure what you mean by filtering out the results for logged on user ... that view should produce a "table" (actually a view, but Fabrik can treat it as a table for the purposes of building a List on it) which has a row for each user, with the totals in it.

I think that SUM() in the view should already be giving total sales?

You'd then just need a calc element, which did something like ...

return (float)'{hughtest___salesquota}' - (float)'{hughtest___sum}';

... or whatever the elements are called.

So have you actually modified that view I did for the 'hughtest' and created the view you need on your new site? I'm getting a little confused about what you've done on which site, and whether what you are talking about now is about the same tables we did this with last time, just on a test server, or if it's for a different application entirely.

-- hugh

 

[rongame]

hi

really sorry, i cant get this to work.

i tried to use the view and tried adding elements to the view using fabrik. unfortunately its not working. i get an error that says Unknown column 'xxxxxx.salesquota_vxxx' in 'field list'.

so how do i add two new columns to the list? or to the view?

i also tried the prefilter for this month and came up with this. its working i hope its right

where field is datetime condition is thismonth value is date_time (type noquotes) public

thanks again

 

[cheesegrits]

Hmm, I thought we'd covered all this in our Skype session back when we created that view. You can't add columns to a MySQL view through Fabrik. A view is not a table. It can be only be treated like a table for purely read-only purposes. To add columns to a view, you have to use the "ALTER VIEW" command in MySQL itself.

Sounds like we need another Skype session to get that view created properly.

Then we can worry about the filtering. Although based on your description above, no that's not quite right. You don't need to set a value/type if you use the "this month" option for the condition. Choosing "this month" provides all the information we need to do the filtering.

-- hugh

 

[rongame]

what if i try to create a new table and just grab or display the elements from the view to a new element from the new table? this way i dont need to alter the view.

is this possible?
i tried to do this using

print (int) '{full___element_name}'; or echo (int) '{full___element_name}';

but nothings happening.

thanks again

 

[cheesegrits]

Nope, that won't work.

Altering the view isn't a huge deal. You just have to do it from within phpMyAdmin.

You are on live chat right now, we'll take a look.

-- hugh

 

[rongame]

hi hugh

any news on this please? hope i can have a solution as soon as possible.

thanks again

 

[rongame]

hi hugh,

friendly follow up on this.

anyway since the "view" is read only, will altering the view help me place new data on that extra column? friendly reminder that what i need to accomplish is get the monthly totals from the view, get the sales quota and the difference which will be manually added from (2)two different element or in this case will come from altering the view.

thanks, really waiting for the solution...

thanks

 

[cheesegrits]

I'm going to need to work with you on Skype / live chat for this, as I need to get at the phpMyAdmin for both the old site where we first built that view, and the new site where you want to rebuild it, and neither of those details are in your My Sites.

-- hugh

 

[rongame]

hi guys

still waiting on this one...

thanks